How to use INDEX and MATCH
Introduction
Apart from VLOOKUP, INDEX and MATCH is the most widely used tool in Excel for performing lookups. The INDEX and MATCH combo is potent and flexible, and you'll see it used in all kinds of formulas, from basic to very advanced.
However, while VLOOKUP allows you to perform lookups with a single function, INDEX and MATCH requires two functions, one nested inside another. Many users find this confusing, because they aren't used to combining functions in Excel, so they avoid INDEX and MATCH. That's a shame. Combining functions is the key to more advanced formulas in Excel.
This article explains in simple terms how to use INDEX and MATCH together to perform lookups. It takes a step-by-step approach, first explaining INDEX, then MATCH, then showing you how to combine the two functions together to create a dynamic two-way lookup.
INDEX – get value at known position
The INDEX function in Excel is fantastically flexible and powerful, and you'll find it in a huge number of Excel formulas, especially advanced formulas. But what does INDEX actually do? In a nutshell, INDEX retrieves values at a given location in a list or table. For example, let's say you have a table of planets in our solar system (see below), and you want to get the name of the 4th planet, Mars, with a formula.
You can do it with this simple formula based on the INDEX function:
=INDEX(B3:B11,4)
Video: How to look things up with INDEX
INDEX and 2D lookups
What if you want to get the diameter of Mars? In that case, we can give INDEX both a row number and a column number, and feed in a larger array (range) of data. The INDEX formula below uses the full range of data in B3:D11, with a row number of 4 and column number of 2:
=INDEX(B3:D11,4,2)
How INDEX can be used to retrieve a value in a 2D table.
To summarize, INDEX gets a value at a specified location in a range of cells based on numeric position. 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 number.
At this point, you may be thinking "So what? How often do you actually know the position of something in a spreadsheet?"
Exactly right. We need a way to locate the position of things we're looking for.
Enter the MATCH function.
MATCH – find position in a list
The MATCH function is designed for one purpose: find the numeric position of an item in a list. For example, we can use MATCH to get the position of the word "peach" in this list of fruits like this:
=MATCH("peach",B3:B9,0)
MATCH returns 3, since "Peach" is the third item in the range. Notice MATCH is not case-sensitive.
MATCH doesn't care if the list is horizontal or vertical. If we arrange the list of fruits horizontally, we get the same result with this MATCH formula:
=MATCH("peach",C4:I4,0)
The MATCH function with a horizontal range. Same result, MATCH returns 3.
Video: How to use MATCH for exact matches
Match type – approximate or exact?
With the MATCH function, the first argument is the lookup value, the second is the lookup array, and the third argument is match type. Match type is important and controls whether matching is exact or approximate. The table below summarizes the options:
| Match type | Behavior | Details |
|---|---|---|
| 1 | Approximate | MATCH finds the largest value less than or equal to lookup value. Lookup array must be sorted in ascending order. |
| 0 | Exact | MATCH finds the first value exactly equal to lookup value. Lookup array does not need to be sorted. |
| -1 | Approximate | MATCH finds the smallest value greater than or equal to lookup value. Lookup array must be sorted in descending order. |
| Approximate | When match type is omitted, it defaults to 1 (approximate). |
The bottom line?
For basic exact match lookups, you'll want to specify zero, to force an exact match. However, if you need an approximate match to lookup values along a scale, you'll want to use either 1 or -1, and you'll need to sort the lookup array according to your needs, [A-Z] when match type is 1, [Z-A] when match type is -1.
Caution: Like VLOOKUP, the MATCH function defaults to an approximate match. Approximate matching is useful when you want to find the best match in a scale of values, but it can be a disaster when you need an exact match. I recommend always setting match type explicitly to avoid unwelcome surprises.
Excel formula video training - quick, clean, and to the point
INDEX and MATCH together
Now that we've covered the basics of INDEX and MATCH, how can we combine the two functions in a single formula? Consider the data below, a table showing a list of salespeople and monthly sales numbers for three months: January, February, and March.
Let's say we want to write a formula that returns the sales number for February for any given salesperson. From the discussion above, we know we can give hardcoded values to INDEX to retrieve a value. For example, to return the February sales number for Frantz, this INDEX formula will do the job:
=INDEX(C3:E11,5,2)
Here we ask INDEX for the value in row 5, column 2 from the range C3:E11, and INDEX returns $5,194.
But we obviously don't want to hardcode values. Instead, we want a dynamic lookup.
Working one step at a time, let's leave the column hardcoded as 2 and make the row number dynamic. How will we do that? The MATCH function of course! MATCH will work perfectly for finding the position of Frantz in the list of names. Here's a revised formula, using the MATCH function, inside of INDEX. Notice we have replaced the number 5 with MATCH:
=INDEX(C3:E11,MATCH("Frantz",B3:B11,0),2)
When MATCH finds "Frantz" in B3:B11, it returns the number 5 directly into INDEX:
=INDEX(C3:E11,5,2)
Naturally, we don't want to hardcode the name either. What we need is a way to input *any* name, then let INDEX and MATCH do their magic.
The screen below shows a very simple implementation of this idea. The formula in H3 is:
=INDEX(C3:E11,MATCH(H2,B3:B11,0),2)
MATCH uses the name in H2 to find a row number for INDEX.
To summarize: INDEX wants numeric positions. MATCH finds those positions.
Let's now tackle the column number.
Two-way lookup with INDEX and MATCH
Above, we used the MATCH function to find the row number for any given salesperson. To keep things simple, we hardcoded the column number 2 to get sales for February. How can we make the formula fully dynamic, so we can can return sales for any given salesperson in any given month? The trick is to use MATCH twice – once to get a row position, and once to get a column position.
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. For example, this formula return the position of March, which is 3:
=MATCH("Mar",C2:E2,0)
But of course we don't want to hardcode any values, so let's update the worksheet to allow the input of a month name, and use MATCH to find the column number we need. The screen below shows the result:
We now have a fully dynamic, two-way lookup with INDEX and MATCH, and the formula looks like this:
=INDEX(C3:E11,MATCH(H2,B3:B11,0),MATCH(H3,C2:E2,0))
The first MATCH formula returns 5 to INDEX as the row number, the second MATCH formula returns 3 to INDEX as the column number. Once MATCH runs, the formula simplifies to:
=INDEX(C3:E11,5,3)
and INDEX correctly returns $10,525, the sales number for Frantz in March.
Note: you could use Data Validation to create simple dropdown menus for both salesperson and month.
Video: How to do a two-way lookup with INDEX and MATCH
Video: How to debug a formula with F9 (to see MATCH return values)
More examples of INDEX + MATCH
Here are some other examples of INDEX and MATCH in action, each with a detailed explanation:
What about VLOOKUP?
Spoiler first: INDEX + MATCH can do everything VLOOKUP can do, and more. It's simply a more flexible way to look things up. Here are a few advantages over VLOOKUP:
However, there is no reason not to use VLOOKUP when the situation fits. It's an excellent function with it's own benefits:
Reasons you may want to use INDEX + MATCH instead of VLOOKUP:
I generally default to VLOOKUP, and switch to INDEX + MATCH when needed.
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.