Contents
All classic search and type substitution functions VPR (VLOOKUP), GPR (HLOOKUP), PÊKIRÎ DERKETIN (WEKWÎ) and those like them have one important feature – they search from the beginning to the end, i.e. left-to-right or top-to-bottom in the source data. As soon as the first matching match is found, the search stops and only the first occurrence of the element we need is found.
What to do if we need to find not the first, but the last occurrence? For example, the last transaction for the client, the last payment, the most recent order, etc.?
Method 1: Finding the Last Row with an Array Formula
If the original table does not have a column with a date or a serial number of a row (order, payment …), then our task is, in fact, to find the last row that satisfies the given condition. This can be done with the following array formula:
Vir:
- Karî IF (GER) checks all cells in a column one by one Miştirî and displays the line number if it contains the name we need. The line number on the sheet is given to us by the function XET (DOR), but since we need the row number in the table, we additionally have to subtract 1, because we have a header in the table.
- Piştre fonksiyonê MAX (MAX) selects the maximum value from the formed set of row numbers, i.e. the number of the most recent line of the client.
- Karî NAVEROK (NAVEROK) returns the contents of the cell with the found last number from any other required table column (Siparîşa kodê bikin).
All this must be entered as formula array, i.e.:
- In Office 365 with the latest updates installed and support for dynamic arrays, you can simply press Derbasbûn.
- In all other versions, after entering the formula, you will have to press the keyboard shortcut Ctrl+Tarloqî+Derbasbûn, which will automatically add curly braces to it in the formula bar.
Method 2: Reverse lookup with the new LOOKUP function
I already wrote a long article with a video about a new feature DÎTINÎ (XLOOKUP), which appeared in the latest versions of Office to replace the old VLOOKUP (VLOOKUP). With the help of BROWSE, our task is solved quite elementarily, because. for this function (unlike VLOOKUP), you can explicitly set the search direction: top-down or bottom-up – its last argument (-1) is responsible for this:
Method 3. Search for a string with the latest date
If in the source data we have a column with a serial number or a date that plays a similar role, then the task is modified – we need to find not the last (lowest) line with a match, but the line with the latest (maximum) date.
I have already discussed in detail how to do this using classic functions, and now let’s try to use the power of the new dynamic array functions. For greater beauty and convenience, we also convert the original table into a “smart” table using a keyboard shortcut Ctrl+T an jî ferman dike Mal - Wek tabloyê format bikin (Mal - Format wek Tablo).
With their help, this “killer couple” solves our problem very gracefully:
Vir:
- Function first PARZÛN (PARZÛN) selects only those rows from our table where in the column Miştirî – the name we need.
- Piştre fonksiyonê pola (JIBERHEVXISTIN) sorts the selected rows by date in descending order, with the most recent deal at the top.
- Karî NAVEROK (NAVEROK) extracts the first row, i.e. returns the last trade we need.
- And, finally, the external FILTER function removes the extra 1st and 3rd columns from the results (Siparîşa kodê bikin и Miştirî) and leaves only the date and amount. For this, an array of constants is used. {0;1;0;1}, defining which columns we want (1) or do not want (0) to display.
Method 4: Finding the Last Match in Power Query
Well, for the sake of completeness, let’s look at a solution to our reverse search problem using the Power Query add-in. With her help, everything is solved very quickly and beautifully.
1. Let’s convert our original table into a “smart” one using a keyboard shortcut Ctrl+T an jî ferman dike Mal - Wek tabloyê format bikin (Mal - Format wek Tablo).
2. Load it into Power Query with the button Ji Tablo/Range tab Jimare (Daneyên - Ji Tablo/Range).
3. We sort (through the drop-down list of the filter in the header) our table in descending order of date, so that the most recent transactions are on top.
4… Di tabloyê de veguherîna tîmek hilbijêre Koma ji hêla (Veguheztin - Kom By) and set the grouping by customers, and as an aggregating function, select the option Hemû xetên (Hemû rêz). You can name the new column anything you like – for example Details.
After grouping, we will get a list of unique names of our clients and in the column Details – tables with all the transactions of each of them, where the first line will be the latest transaction, which is what we need:
5. Add a new calculated column with the button Stûna Custom tab Stûnê zêde bikin (Add column — Add custom column)û formula jêrîn binivîse:
vir Details – this is the column from which we take tables by customers, and 0 {} is the number of the row we want to extract (row numbering in Power Query starts from zero). We get a column with records (Rekor), where each entry is the first row from each table:
It remains to expand the contents of all records with the button with double arrows in the column header Last deal selecting the desired columns:
… and then delete the column that is no longer needed Details by right-clicking on its title – Stûnan rakin (Remove columns).
After uploading the results to the sheet through Mal - Girtin û barkirin - Girtin û barkirin (Mal - Girtin & Barkirin - Girtin & Barkirin ...) we will get such a nice table with a list of recent transactions, as we wanted:
When you change the source data, you must not forget to update the results by right-clicking on them – the command Nûvekirin & Save (Hênikkirin) an jî kurteya klavyeyê Ctrl+alt+F5.
- The LOOKUP function is a descendant of VLOOKUP
- How to use the new dynamic array functions SORT, FILTER, and UNIC
- Bi fonksiyona LOOKUP-ê di rêzek an stûnê de şaneya ne-vala ya dawîn bibînin