Contents
All classic search and type substitution functions VPR (VLOOKUP), GPR (HLOOKUP), MORE EXPOSED (MATCH) 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:
Here:
- Function IF (IF) checks all cells in a column one by one Customer 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 LINE (ROW), but since we need the row number in the table, we additionally have to subtract 1, because we have a header in the table.
- Then the function 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.
- Function INDEX (INDEX) returns the contents of the cell with the found last number from any other required table column (Order code).
All this must be entered as array formula, i.e.:
- In Office 365 with the latest updates installed and support for dynamic arrays, you can simply press Enter.
- In all other versions, after entering the formula, you will have to press the keyboard shortcut Ctrl+Shift+Enter, 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 Ctrl+T or commands Home – Format as a table (Home — Format as Table).
With their help, this “killer couple” solves our problem very gracefully:
Here:
- Function first FILTER (FILTER) selects only those rows from our table where in the column Customer – the name we need.
- Then the function GRADE (SORT) sorts the selected rows by date in descending order, with the most recent deal at the top.
- Function INDEX (INDEX) 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 (Order code и Customer) 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 or commands Home – Format as a table (Home — Format as Table).
2. Load it into Power Query with the button From Table/Range tab Data (Data — From Table/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… In the tab Transformation choose a team Group by (Transform — Group By) and set the grouping by customers, and as an aggregating function, select the option All lines (All rows). 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 Custom column tab Add column (Add column — Add custom column)and enter the following formula:
Here 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 (Record), 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 – Remove columns (Remove columns).
After uploading the results to the sheet through Home — Close and load — Close and load in (Home — Close & Load — Close & Load to…) 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 Update & Save (Refresh) or keyboard shortcut Ctrl+Alt+F5.
- The LOOKUP function is a descendant of VLOOKUP
- How to use the new dynamic array functions SORT, FILTER, and UNIC
- Finding the last non-blank cell in a row or column with the LOOKUP function