How to use INDEX and MATCH (2024)

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. There are more advanced examples further down the page.

Table of Contents

  • The INDEX Function
  • The MATCH function
  • INDEX and MATCH together
  • Two-way lookup with INDEX and MATCH
  • Left lookup with INDEX and MATCH
  • INDEX and MATCH with multiple criteria
  • Case-sensitive lookup
  • Finding the closest match
  • INDEX and XMATCH
  • 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(B3:B11,4)

How to use INDEX and MATCH (1)
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(B3:D11,4,2)

How to use INDEX and MATCH (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 numbers.

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("peach",B3:B9,0)

How to use INDEX and MATCH (3)
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:

=MATCH("peach",C4:I4,0)

How to use INDEX and MATCH (4)
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.

How to use INDEX and MATCH (5)

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:

=INDEX(C3:E11,5,2) // returns $5194

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:

=INDEX(C3:E11,MATCH("Frantz",B3:B11,0),2)

Taking things one step further, we'll use the value from H2 in MATCH:

=INDEX(C3:E11,MATCH(H2,B3:B11,0),2)

How to use INDEX and MATCH (6)
MATCH finds "Frantz" and returns 5 to INDEX for row.

To summarize:

  1. INDEX needs numeric positions.
  2. MATCH finds those positions.
  3. MATCH is nested inside INDEX.

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:

=MATCH("Mar",C2:E2,0) // returns 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:

How to use INDEX and MATCH (7)
A fully dynamic, two-way lookup with INDEX and MATCH.

=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, and 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 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:

How to use INDEX and MATCH (8)

Read a detailed explanation here.

Index and Match with multiple criteria

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. A nice way to handle these problems is to use Boolean logic, a technique for handling TRUE and FALSE values like 1s and 0s. You can see this approach below, where we areusing INDEX and MATCH and Boolean logic to find a price based on three values: Item, Color, and Size:

How to use INDEX and MATCH (9)

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 in Excel 2019 and older.

For a quick introduction to Booleans in Excel see these videos from our Dynamic Array Formulas course:

  • Introduction to Booleans
  • Boolean Algebra in Excel

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:

How to use INDEX and MATCH (10)

Read a detailed explanation here.

Note: this is an array formula and must be entered with control + shift + enter in Excel 2019 and older.

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.

How to use INDEX and MATCH (11)

Read a detailed explanation here.

Note: this is an array formula and must be entered with control + shift + enter in Excel 2019 and older.

INDEX and XMATCH

The current version of Excel includes the XMATCH function, which is an upgraded replacement for the MATCH function. Like the MATCH function, XMATCH performs a lookup and returns a numeric position. Also like MATCH, XMATCH can perform lookups in vertical or horizontal ranges, supports both approximate and exact matches, and allows wildcards (* ?) for partial matches. But XMATCH adds even more features. The 5 key differences between XMATCH and MATCH are as follows:

  1. XMATCH defaults to an exact match, while MATCH defaults to an approximate match.

  2. XMATCH can find the next larger item or the next smaller item.

  3. XMATCH can perform a reverse search (i.e. search from last to first).

  4. XMATCH does not require values to be sorted when performing an approximate match.

  5. XMATCH can perform a binary search, which is specifically optimized for speed.

So, can you simply use XMATCH in an INDEX and MATCH formula instead of MATCH? Yes, absolutely. Using XMATCH instead of the MATCH function "upgrades" the formula to include the benefits listed above.

Using XMATCH instead of the MATCH "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. You can simply change "MATCH" to "XMATCH" as shown below:

=MATCH(value,array,0) // exact match=XMATCH(value,array,0) // exact match

Note: since XMATCH defaults to an exact match, the zero above is not required. However, when converting MATCH in exact-match mode to XMATCH, you can leave the zero if you like.

For approximate matches, XMATCH behavior is different when match_type is set to 1:

=MATCH(value,array,1) // exact match or next smallest=XMATCH(value,array,1) // exact match or next *largest*

In addition, XMATCH allows -1 for match type, which is not available with MATCH:

=XMATCH(value,array,-1) // exact match or next smallest

Note: the MATCH function does not offer the search mode argument at all.

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.

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)
How to use INDEX and MATCH (2024)

FAQs

How can we use INDEX & Match together? ›

Left Lookup in Excel
  1. Step 1: Extract Row Position. First extract row position of Cost 140 Rs using the formula: =MATCH(140, D3:D10,0) ...
  2. Step 2: Use INDEX Function. After getting the row number, the next step is to use the INDEX Function to extract Food Name using the formula: =INDEX(B3:B10, MATCH(140, D3:D10,0))
Jun 28, 2024

Why won't my INDEX match formula work? ›

You have used an array formula without pressing Ctrl+Shift+Enter. When you use an array in INDEX, MATCH, or a combination of those two functions, it is necessary to press Ctrl+Shift+Enter on the keyboard. Excel will automatically enclose the formula within curly braces {}.

How do you INDEX match if there is more than one result? ›

You need to use an array formula that combines INDEX, MATCH, SMALL and IF functions to return multiple values horizontally based on a given criteria. And then drag it across horizontally to fill the rest of the cells. This should return the items that match the space code and are approved.

How do you use INDEX match for approximate match? ›

This formula uses -1 for match type to allow an approximate match on values sorted in descending order. The MATCH part of the formula looks like this: MATCH(F4,B5:B9,-1) Using the lookup value in cell F4, MATCH finds the first value in B5:B9 that is greater than or equal to the lookup value.

What is better, VLOOKUP or INDEX match? ›

INDEX/MATCH is more secure because you can't change the column to be shown as you can in a VLOOKUP range. INDEX/MATCH is also structurally better because moving columns around won't break. VLOOKUP could show something completely different.

What are the rules for INDEX and match in Excel? ›

You can use the INDEX and MATCH functions to find a specific value from a table and match it with another value. You can do so by first using INDEX to retrieve a value from the table based on its location and then correlating it with a different value by using MATCH.

Can you do INDEX match with 2 criteria? ›

To perform an INDEX MATCH with multiple criteria in Excel, simply use an ampersand (&) to place multiple references in your lookup value and lookup array inputs in the MATCH formula.

Is there a limit to INDEX match in Excel? ›

Which version of Excel? With a quick googling, MATCH has a 255 character limit. According to this post from July -22 in Stack Overflow, If you have the latest version of Excel, you can use the XMATCH function.

How to lookup value in Excel with 2 conditions? ›

One easy way of performing a multiple criteria lookup is by using XLOOKUP with the "&" operator that concatenates all the criteria into one lookup value and their corresponding lookup columns into one lookup array. This approach is more efficient than using VLOOKUP because you don't need the helper column.

What is the difference between xlookup and index match? ›

XLOOKUP and INDEX XMATCH can do any matching on unsorted data, thanks to the inherent features. Reverse search. While INDEX MATCH can only look up from the beginning to the end, both XLOOKUP and INDEX + XMATCH can search in both directions: from first to last and from last to first.

How to fuzzy match in Excel? ›

Create a fuzzy match (Power Query)
  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. ...
  2. Select Home > Combine > Merge Queries. ...
  3. Select the column you want to use for your fuzzy match.

How do you find the exact match in index match? ›

MATCH function

Note that the match_type argument is set to zero (0), to force MATCH to perform an exact match.

How do you combine INDEX match and sum? ›

INDEX & MATCH points to a single cell corresponding to the first match for the search value in the MATCH. It probably isn't going to do what you want. If you want to sum the values in column B for all matches to Product A in column A, you might use either of: =SUMIF(A2:A10,"Product A",B2:B10)

What is the difference between xlookup and INDEX match? ›

XLOOKUP and INDEX XMATCH can do any matching on unsorted data, thanks to the inherent features. Reverse search. While INDEX MATCH can only look up from the beginning to the end, both XLOOKUP and INDEX + XMATCH can search in both directions: from first to last and from last to first.

Can you use an INDEX match across multiple sheets? ›

INDEX MATCH will work whether your data is listed in one tab or on multiple tabs in your spreadsheet.

Can you INDEX match two columns? ›

By adding 0 and 1 to the column number returned by XMATCH, we end up with two numbers in an array {3;4} that are provided to the INDEX function as column_num. The result is that INDEX returns values from both columns, and these values spill into the range K11:K12 when using a current version of Excel.

References

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Carmelo Roob

Last Updated:

Views: 6432

Rating: 4.4 / 5 (65 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Carmelo Roob

Birthday: 1995-01-09

Address: Apt. 915 481 Sipes Cliff, New Gonzalobury, CO 80176

Phone: +6773780339780

Job: Sales Executive

Hobby: Gaming, Jogging, Rugby, Video gaming, Handball, Ice skating, Web surfing

Introduction: My name is Carmelo Roob, I am a modern, handsome, delightful, comfortable, attractive, vast, good person who loves writing and wants to share my knowledge and understanding with you.