Index Match in Excel

Index Match combines two of Excel’s most powerful and useful functions, INDEX and MATCH, into a single formula. This gives you flexibility to pull specific values from even the largest datasets. This has a variety of practical applications, as you’ll soon see. Let’s learn how to use Index Match in Microsoft Excel.

The INDEX and MATCH Functions in Excel

The term “Index Match” is a bit misleading in its title. Rather than being a standalone function, it’s a combination of two. In essence, the functions nest together inside a formula. Before diving into these combinations, let’s quickly review what each function does individually.

INDEX returns a value from a range, working across both rows and columns. For example, imagine a list of ten names like this. You want to find the seventh name in the list and display it in a separate cell. Click into a cell to begin a formula and type:

=INDEX(A1:A10,7), where A1:A10 is your range and 7 is the location in the range. Hit Enter, and the name appears in the cell.

MATCH works similarly, but it returns the position of a value in a range, not the actual value.

For example, imagine a row listing six cities. You want to know the position of Miami in the range. In an empty cell, type:

=MATCH(“miami”,C1:H1,0), where “miami” is your lookup value, C1:H1 is your range, and 0 signifies an exact match. Notice that the lookup value is not case-sensitive.

Hit Enter, and the position from left to right is displayed: 4.

How to Combine INDEX and MATCH in Excel

Now that you understand the basics of INDEX and MATCH and how they work, it’s time to bring the two functions together.

Let’s now imagine a more complex arrangement of data. Consider a multi-column range of products with their serial numbers, names, and prices listed. These are spread over columns A, B, and C.

In cell F1, you want to be able to type in the serial number and have the price return in the cell below it, F2. This is a great time to merge INDEX and MATCH together.

A key rule of thumb is that, when you combine these two functions, INDEX always goes first, with MATCH following – nested – within the INDEX function.

Begin by clicking into cell F2, where you want the price to display. Type = on your keyboard, telling Excel that you’re beginning to input a formula. Then, add your index function:

=INDEX(

Then, click and drag to select the range of prices, which is the range covering C2:C6. Then, type a comma. So far, your formula is:

=INDEX(C2:C6,

You have the INDEX function looking in the correct column now. What then needs to occur is for the function to know which serial number’s price to pull from the column. This is done by nesting the MATCH function.

Type MATCH(, and then click on cell F1. Remember, you want to be able to change this number and have Excel automatically return the product price:

=INDEX(C2:C6,MATCH(F1

Add another comma, select the serial number range, A2:A6, and then type in a 0 to ensure an exact match. Close with a double parentheses, )), to enclose the MATCH function as well as the complete INDEX function.

Your complete formula reads:

=INDEX(C2:C6,MATCH(F1,A2:A6,0))

You now have a dynamic formula built using INDEX and MATCH. Type any serial number into cell F1, then hit Enter on your keyboard. Excel will instantly display the appropriate price for the product in question.

How to Look Left with INDEX and MATCH

Astute users may notice that the above example would be perfectly workable when formatted as a VLOOKUP. But this technique has a key advantage over VLOOKUP. Remember, VLOOKUP looks only to the right across a dataset. Using a similar version of our data, let’s view it in the order of Price – Serial # – Name.

Let’s say you want to look up the price by inputting the serial number. That’s impossible with a VLOOKUP in this orientation, because the price is listed to the left of the lookup value, which is the serial number. But a combination of INDEX and MATCH will handle the job neatly.

Click into cell F2, and start by inserting the INDEX function:

=INDEX(

Remember, the value you want is in column A, the price. Click and drag to select A2:A6, the range of prices. Type a comma, then add your MATCH function. The formula, so far, is:

=INDEX(A2:A6,MATCH(

You want to change cell F1 by inputting a serial number, in order to have F2 return the correct price. Therefore, type F1. It’s a lookup value, so type a comma, then select the range of serials, B2:B6:

=INDEX(A2:A6,MATCH(F1,B2:B6

And finally, wrap up with a comma, followed by 0 for an exact match, and two closed parentheses:

=INDEX(A2:A6,MATCH(F1,B2:B6,0))

Now, when you input a serial number in cell F1, the price will display in cell F2. This wouldn’t be possible with a VLOOKUP with the layout and arrangement of your data.

Two-Way Lookups with INDEX and MATCH

Another useful feature of INDEX and MATCH is the ability to perform what Excel calls two-way lookups. In the example above, you constructed a function to search for a price based on a serial number input. Now, imagine a more complex dataset where you want to do an advanced search.

Imagine that you sell products in three sizes and three colors. You can buy small, medium, or large, in red, blue, or green. You have the prices for each of the nine options listed in a table. The leftmost column contains the sizes, and the three adjacent columns contain the colors. You want to be able, at a moment’s notice, to identify the price of a medium green product, for example.

This time, we’ll let cells G1 and G2 be the pair of inputs, while cell G3 will be the price output. Click into cell G3. By now, you’re familiar with the syntax, and won’t be surprised to learn that step one is to type =INDEX(. As mentioned above, this is always how you combine the INDEX and MATCH functions.

INDEX here needs to encompass the full range of values, B2:D4. Then, add the first of two MATCH functions:

=INDEX(B2:D4,MATCH(

Click cell G1, then select the range of sizes in your table, A2:A4, then type 0 for an exact match:

=INDEX(B2:D4,MATCH(G1,A2:A4,0)

Conclude with a second MATCH to capture the range of colors:

=INDEX(B2:D4,MATCH(G1,A2:A4,0),MATCH(G2,B1:D1,0))

Now, you can specify inside cells G1 and G2 exactly what size and color combination you want. Excel will run the INDEX and paired MATCH functions to return your result. For instance, try out Medium and Green, and cell G3 will return a price of $37.00 from your data.

It’s easy to see the power of INDEX and MATCH, even as individual functions. But when you combine them together, you’ll find yourself working with an absolute powerhouse formula right inside Microsoft Excel.

It renders even the most complex and large datasets easily searchable. After all, even the best data isn’t worth much if it isn’t helping you. Give the INDEX and MATCH combination a try next time you need to work with a large volume of data in a quick, easy, and interactive way.