Site icon Professor Excel

INDEX and MATCH: Great Alternative to VLOOKUP in Excel!

index, match, excel, formula, vlookup

You’ve probably heard of the VLOOKUP formula in Excel, haven’t you? The VLOOKUP formula searches for a value in a column. Once found it returns another value from the same row. A combination of INDEX and MATCH serves the same purpose. It works slightly different and has therefore some advantages and disadvantages towards VLOOKUP.

First things first:

The INDEX formula

As INDEX and MATCH are two separate formulas, we take a look at them separately before putting them together.

The INDEX formula returns the n-th value from an array of cells.

Let’s say we got a range of 4 cells, for example D3:D6 (blue range in the picture above). We want to know the value of cell D5. As D5 is the 3rd value in this range, we type the following formula:

=INDEX(D3:D6;3)

The first part (number one on the picture), defines the array of cells which we want to get the return value from. The second part of the formula says, that we want to get the value of the third cell in this range.

The MATCH formula

With MATCH, you can search for a value within a range of cells. Once found, MATCH returns the location of the first occurrence.

An example: We got a cell range B3 to B6 with car brands (the red cell area in the picture above). We want to search for the brand “BMW” within this cell range. The result should be the number of the cell. Counting starts with the first cell of the range.

=MATCH("BMW",B3:B6,0)

So we search for “BMW” (first part of the formula, number 2 in the picture above), within the range B3 to B6 (second part of the formula, number 3 in the picture above). The third and last part of the formula is a little bit tricky:

In our case we search for the exact name “BMW” so that we have to fill in 0 as the last part of the formula.

The combination of INDEX and MATCH

How to use the INDEX-MATCH formula combination in Excel

We now know how to use INDEX and MATCH separately so that we can combine them. In our example, we search for BMW in the first column (column B) and want to get it’s price from column D.

The formula has 4 relevant parts (the numbers are corresponding to the picture above):

  1. The first part refers to the cells containing the value that we want to get returned. In our case it’s the price.
  2. With the MATCH formula we will get the location of the first cell, that says “BMW”. The first part of the MATCH formula is the keyword we search for.
  3. The second part of the MATCH formula contains the range of cells, we search “BMW” within.
  4. We have to determine that we want to find the exact term “BMW” by adding “0” as the fourth part of the formula.

The complete formula looks like this:

=INDEX(D3:D6,MATCH("BMW",B3:B6,0))

Hold on a second. Was this information helpful so far?

Connect with me:

Boost your Excel skills: Learn the best Excel tricks and tutorials!


Advantages and disadvantages of INDEX and MATCH towards VLOOKUP

A combination of the two formulas INDEX and MATCH has some advantages and disadvantages towards VLOOKUP:

Advantages

Disadvantages

If you want to know more about when to use VLOOKUP, SUMIFS or INDEX and MATCH, please have a look at this article.

Take it one step further: 2 way lookups with INDEX – MATCH – MATCH

Method 4: The INDEX/MATCH/MATCH combination.

Above you’ve learned the simple version of the INDEX formula in Excel. But there is an advanced way to use it too: You can not only provide one dimension for a lookup, but also a second one. That way, you can create a 2 way- or 2D lookup.

Instead of just a simple INDEX/MATCH combination, you can add one more dimension with the second MATCH. The formula looks like this:

=INDEX(B2:E8,MATCH(C11,B2:B8,0),MATCH(C12,B2:E2,0))

The INDEX formula has 3 parts here: The complete cell range (B2 to E8) and two MATCH formulas.

  1. The return cell range stretches over the hole table. In our case that’s B2 to E8.
  2. The first MATCH searches within the range B2 to B8 for the country.
  3. The second MATCH looks for the type in the cell range B2 to E3 (the headline).

Both MATCH formulas return the number of the cell in with the search term the first time comes up.

Do you want to know more about the 2D lookup in Excel? Please refer to this article.

Exit mobile version