This article explains in simple terms how to use INDEX and MATCH together to perform lookups.
There are more advanced examples further down the page.
But what does INDEX actually do?
In a nutshell, INDEX retrieves the value at a given location in a range.
When the range is one-dimensional, you only need to supply a row number.
When the range is two-dimensional, you’ll need to supply both the row and column numbers.
At this point, you may be thinking “So what?
How often do you actually know the position of something in a spreadsheet?”
We need a way to locate the position of things we’re looking for.
Enter the MATCH function.
MATCH is not case-sensitive.
Match_type is important and controls whether matching is exact or approximate.
In many cases, you will want to use zero (0) to force exact match behavior.
Match_type defaults to 1, which means approximate match, so it’s important to provide a value.
See theMATCH pagefor more details.
Instead, we want adynamiclookup.
How will we do that?
The MATCH function of course.
MATCH will work perfectly for finding the positions we need.
To summarize:
Let’s now tackle the column number.
From the examples above, we know MATCH works fine with both horizontal and vertical arrays.
That means we can easily find the position of a given month with MATCH.
The screen below shows the result:
A fully dynamic, two-way lookup with INDEX and MATCH.
Note: you could useData Validationto create dropdown menus to select salesperson and month.
In other words, a lookup that matches on more than one column at the same time.
you could use thissame approach with XLOOKUP.
This makes it easy to customize behavior to match specific data patterns.
For example, you’re free to use astep-based formulato jump directly to relevant data.
Case-sensitive lookup
By itself, the MATCH function is not case-sensitive.
Essentially, we use MATCH to find the smallest difference.
Then, we use INDEX to retrieve the associated trip from column B.
Like the MATCH function, XMATCH performs a lookup and returns a numeric position.
But XMATCH adds even more features.
Using XMATCH instead of the MATCH function “upgrades” the formula to include the benefits listed above.
Replacing MATCH with XMATCH
For exact-match problems, XMATCH is a drop-in replacement for the MATCH function.
XMATCH can also be configured to perform a reverse search and a binary search.
For a full description of all of the options available with XMATCH,see this page.
