The data is sorted by date in ascending order.
The goal is to look up the latest order for a given person by Name.
In other words, we want the last match by name.
The challenge is that Excel lookup formulas will return the first match by default.
There are several ways to approach this problem.
In the current version of Excel, a good option is the XLOOKUP function.
Another more flexible approach is to use the FILTER function with the TAKE function.
All three approaches are explained below.
When it finds a match, it returns the entire matching row from thedatatable.
Notice we leaveif_not_foundandmatch_modeempty, but we still need to supply the commas.
It is not necessary to return the entire row.
To return just the amount, simply adjustreturn_array, as needed.
Excel Tables use structured references likedata[Name].
SeeExcel Tablesfor a complete overview.
This is a more flexible approach because it can be used to get the “last n matches”.
nth to last match
If you want the nth to last match (i.e.
the second to the last match, the third to the last match, etc.)
CHOOSEROWS then returns the second to last row from the FILTER result.
XMATCH function
The example shown returns thedatafrom the location of the last match.
The result is 8 since the last match is in the eighth row of the table.
Let’s look at how this works step by step.
Next, we divide the number 1 by this array.
This may seem strange, but it is intentional.
We use 2 as a lookup value to force LOOKUP to scan to theend of the data.
LOOKUP will automatically ignore errors, so the only thing left to match are the 1s.
It will scan through the 1s looking for a 2 that can never be found.
All other values become FALSE since they failed the logical test in the IF function.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
The number of rows and columns to return is provided by separaterowsandcolumnsarguments.
Rows and columns can be extracted from the start or end of the given array.
LOOKUP’s default behavior makes it useful for solving certain problems in Excel.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
The MATCH function is often used together with INDEX to provide row and column numbers….
