Depending on your needs, FILTER might be a better option than first or last match.
Read on for the answer and more interesting examples.
It doesn’t matter if data is sorted or not.
In the screen below, the lookup value in E5 is “red”.
In the screen below, VLOOKUP is set to approximate match mode, and colors are sorted.
With unsorted data, you may see normal-looking results that are totally incorrect.
This problem is more likely with VLOOKUP becauseVLOOKUP defaults to approximate matchwhen no fourth argument is provided.
To illustrate this problem, see the example below.
Data isunsortedand VLOOKUP, with no fourth argument provided, defaults to approximate match.
We’ll look at how to overcome the problem of last match and unsorted data below.
If so, don’t worry.
Using approximate match to get the last match is not the “normal” case.
Typically, you’ll see approximate match used to assign values according to some kind of scale.
Practical applications
How can we use the behavior described above in a practical situation?
For example, below we are using VLOOKUP in approximate match mode to find the latest price for Sandals.
Below, we are using an equivalentINDEXandMATCHformula find the latest price with the same data.
LOOKUPalwaysperforms an approximate match, so it works well in “last match” scenarios.
This means we can dynamically build a lookup array to locate the data we want using simple logical expressions.
You may not have seen a formula like this before, so let’s break it down in steps.
This may seem puzzling.
How will LOOKUP ever find the number 2 in an array that contains only 1s and errors?
We are using 2 as a lookup value to force LOOKUP to scan to theend of the data.
The LOOKUP function 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 will never be found.
However, you’re free to certainly use an array formula if you like.
Also, the newerXMATCH functionandXLOOKUP functioncan be directly configured to return the last match.
Last non-blank cell
The approach above turns out to be really useful.
Lookup nth match?
Here are some links for you:
You’ll notice formulas like this get complicated.
There are some cool new functions coming to Excel in 2019 that will make these solutions much simpler.
In the meantime, don’t forget thatPivot Tablesare a great way to explore datawithout formulas.
What’s next?
Below are guides to learn more about Excel formulas.
We also offeronline video training.
