ינהאַלט
All classic search and type substitution functions VPR (VLOOKUP), גפּר (HLOOKUP), מער יקספּאָוזד (מאַטש) 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:
דאָ:
- פונקציע IF (אויב) checks all cells in a column one by one קוינע 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 ליניע (רודערן), but since we need the row number in the table, we additionally have to subtract 1, because we have a header in the table.
- דערנאָך די פֿונקציע מאקס (מאַקס) selects the maximum value from the formed set of row numbers, i.e. the number of the most recent line of the client.
- פונקציע ינדעקס (אינדעקס) returns the contents of the cell with the found last number from any other required table column (סדר קאָד).
All this must be entered as מענגע פאָרמולע, i.e.:
- In Office 365 with the latest updates installed and support for dynamic arrays, you can simply press אַרייַן.
- In all other versions, after entering the formula, you will have to press the keyboard shortcut קטרל+יבעררוק+אַרייַן, 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 VIEW (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 קטרל+T אָדער קאַמאַנדז היים - פֿאָרמאַט ווי אַ טיש (היים - פֿאָרמאַט ווי טיש).
With their help, this “killer couple” solves our problem very gracefully:
דאָ:
- Function first פילטער (פילטער) selects only those rows from our table where in the column קוינע – the name we need.
- דערנאָך די פֿונקציע מיינונג (סאָרט) sorts the selected rows by date in descending order, with the most recent deal at the top.
- פונקציע ינדעקס (אינדעקס) 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 (סדר קאָד и קוינע) 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 קטרל+T אָדער קאַמאַנדז היים - פֿאָרמאַט ווי אַ טיש (היים - פֿאָרמאַט ווי טיש).
2. Load it into Power Query with the button פֿון טיש / קייט קוויטל דאַטע (דאַטן - פֿון טיש / קייט).
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... אין די קוויטל טראַנספאָרמאַציע קלייַבן אַ קאָלעקטיוו גרופּע דורך (טראַנספאָרמירן - גרופע לויט) and set the grouping by customers, and as an aggregating function, select the option אַלע שורות (אלע רייען). You can name the new column anything you like – for example דעטאַילס.
After grouping, we will get a list of unique names of our clients and in the column דעטאַילס – 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 מנהג זייַל קוויטל לייג זייַל (Add column — Add custom column)און אַרייַן די פאלגענדע פאָרמולע:
דאָ דעטאַילס – 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 (רעקאָרד), 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 דעטאַילס דורך רעכט-קליקינג אויף זייַן טיטל - אַראָפּנעמען שפאלטן (Remove columns).
After uploading the results to the sheet through היים - נאָענט און לאָדן - נאָענט און לאָדן אין (היים - נאָענט & לאָדן - נאָענט & לאָדן צו ...) 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 דערהייַנטיקן & היט (דערפרישן) אָדער קלאַוויאַטור דורכוועג קטרל+אַלט+F5.
- The LOOKUP function is a descendant of VLOOKUP
- How to use the new dynamic array functions SORT, FILTER, and UNIC
- געפֿינען די לעצטע ניט-ליידיק צעל אין אַ רודערן אָדער זייַל מיט די LOOKUP פונקציע