INDEX function | MATCH function | INDEX and MATCH | 2-way lookup | Left lookup | Case-sensitive | Closest match | Multiple criteria | More examples
The INDEX Function
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 the value at a given location in a range. 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 use INDEX like this: INDEX returns the value in the 4th row of the range. Video: How to look things up with INDEX What if you want to get the diameter of Mars with INDEX? In that case, we can supply both a row number and a column number, and provide a larger range. 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 retrieves the value at row 4, column 2. To summarize, INDEX gets a value at a given 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.
The MATCH function
The MATCH function is designed for one purpose: find the position of an item in a range. For example, we can use MATCH to get the position of the word “peach” in this list of fruits like this: MATCH returns 3, since “Peach” is the 3rd item. MATCH is not case-sensitive. MATCH doesn’t care if a range is horizontal or vertical, as you can see below: Same result with a horizontal range, MATCH returns 3. Video: How to use MATCH for exact matches Important: The last argument in the MATCH function is match_type. 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 the MATCH page for more details.
INDEX and MATCH together
Now that we’ve covered the basics of INDEX and MATCH, how do 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 a given salesperson. From the discussion above, we know we can give INDEX a row and column number to retrieve a value. For example, to return the February sales number for Frantz, we provide the range C3:E11 with a row 5 and column 2: But we obviously don’t want to hardcode numbers. Instead, we want a dynamic lookup. How will we do that? The MATCH function of course. MATCH will work perfectly for finding the positions we need. Working one step at a time, let’s leave the column hardcoded as 2 and make the row number dynamic. Here’s the revised formula, with the MATCH function nested inside INDEX in place of 5: Taking things one step further, we’ll use the value from H2 in MATCH: MATCH finds “Frantz” and returns 5 to INDEX for row. To summarize: 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 dynamically, but hardcoded the column number. How can we make the formula fully dynamic, so we 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 returns the position of March, which is 3: 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: A fully dynamic, two-way lookup with INDEX and MATCH. 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: and INDEX correctly returns $10,525, the sales number for Frantz in March. Note: you could use Data Validation to create dropdown menus to select 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)
Left lookup
One of the key advantages of INDEX and MATCH over the VLOOKUP function is the ability to perform a “left lookup”. Simply put, this just means a lookup where the ID column is to the right of the values you want to retrieve, as seen in the example below:
Read a detailed explanation here.
Case-sensitive lookup
By itself, the MATCH function is not case-sensitive. However, you use the EXACT function with INDEX and MATCH to perform a lookup that respects upper and lower case, as shown below:
Read a detailed explanation here. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.
Closest match
Another example that shows off the flexibility of INDEX and MATCH is the problem of finding the closest match. In the example below, we use the MIN function together with the ABS function to create a lookup value and a lookup array inside the MATCH function. Essentially, we use MATCH to find the smallest difference. Then we use INDEX to retrieve the associated trip from column B.
Read a detailed explanation here. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.
Multiple criteria lookup
One of the trickiest problems in Excel is a lookup based on multiple criteria. In other words, a lookup that matches on more than one column at the same time. In the example below, we are using INDEX and MATCH and boolean logic to match on 3 columns: Item, Color, and Size:
Read a detailed explanation here. You can use this same approach with XLOOKUP. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.
More examples of INDEX + MATCH
Here are some more basic examples of INDEX and MATCH in action, each with a detailed explanation:
Basic INDEX and MATCH exact (features Toy Story) Basic INDEX and MATCH approximate (grades) Two-way lookup with INDEX and MATCH (approximate match)
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
