Lookup Formulas

How to Use INDEX-MATCH, Part 1: The INDEX Function

INDEX-MATCH is Excel's most-powerful lookup method. But many users find it challenging. This is the first of a three-part series designed to INDEX-MATCH clearer.

Which is best, VLOOKUP or INDEX-MATCH?

You might be surpised at the question, because the answer never has been in doubt. INDEX-MATCH can do anything that VLOOKUP and HLOOKUP can do, and a lot more.

Here are five specific reasons that INDEX-MATCH is better than VLOOKUP:

1. It’s never slower than VLOOKUP and can be much faster.
2. It returns a reference rather than a value, which allows us to use it to do more things in our formulas.
3. It doesn’t care where the result array is with regard to the lookup array...even in a different workbook.
4. It can return approximate matches from data sorted largest to smallest.
5. It can look up either vertical or horizontal data, with no need to change functions.

The major problem with INDEX-MATCH is that many Excel users don't know how to use it. That's a real shame, because INDEX and MATCH are very useful functions on their own. Every Excel user in business should know how to use them...individually, and in combination.

So in this article, I'm going to teach you how to use the INDEX function.

In Part 2 of this series, I show you how to use the MATCH function. And in Part 3, I show you six INDEX-MATCH lookups that VLOOKUP can't do.

Finally, in Part 3, I'll show you how to use the two functions in combination. And particularly, I'll show you how to perform types of calculations that VOOKUP and HLOOKUP can't.

At the link you can download the zip file of the example workbooks for all three articles.

The INDEX Function

The INDEX function uses this syntax:

=INDEX(reference, row_num, column_num, area_num)

If reference specifies only one area, or one array—as it usually does—you can ignore the area_num argument, giving you this syntax:

=INDEX(reference, row_num, column_num)

And if reference specifies only a single row or column, you can ignore the third argument in the preceding formula, like this:

=INDEX(reference, row_or_column_num)

Although we can use the area number in an INDEX-MATCH lookup, there's seldom a need to do so. But I'll explain it briefly so you'll know how to use it when you need to.

Specifying the Area Number

In the figure below, the first blue range is area 1, and the second blue range is area 2. The first range is area 1 because I selected that area first.

To select a "discontiguous range" like this, select the first range, hold down your Ctrl key, then select the second range. And then select the third, and so on.

This formula returns a reference to cell D4 in the second area:

=INDEX((A1:B4,D2:F4),3,1,2)

That is, this formula returns a reference to the cell in the third row and the first column of the second area, which is the range D2:F4. That is, it returns the identical result that the formula =$D$4 would return.

Notice in the formula that when you use cell addresses to reference a discontiguous range, you must enclose the references with parentheses. If you don't do this, Excel will think that the first area is the first argument for INDEX, that the second area is the second argument, and so on.

And now that you know what the area_num is about, I'll ignore it, because you'll rarely use it.

Using INDEX to Return References

This is a simple table with the prices for four products in random order. For convenience, I've assigned these names...

• Product—The column of product names.
• Price—The column of prices.
• Data—Both columns of data.
• Header—The row of column titles.

 We can use the INDEX function in formulas to return data from all four ranges. You can download the workbook with these INDEX formulas here.

For example...

Formula 1:  =INDEX(Product,2,1) 

...returns "Coats".

Strictly speaking, Formula 1 returns a reference to the cell that contains "Coats". In other words, Formula 1 returns exactly the same information as...

Formula 2:  =$B$4  

...which also returns "Coats".

If INDEX is referencing only one row or column, Excel lets us skip the second argument. Therefore, this formula...

Formula 3:  =INDEX(Price,4) 

...returns 150.

And this formula...

Formula 4:  =INDEX(Header,2) 

...returns "Price".

The INDEX function also allows you to reference an entire row or column in a range. You do so by either omitting the argument of the row or column for which you want the entire area, or by using a zero for the argument.

To illustrate in this figure, which I've repeated from above, both of these formulas...

Formula 5:  =COUNTA(INDEX(Data,,1))

Formula 6:  =COUNTA(INDEX(Data,0,1))

...return 4, the number of product names in the Data range.

And the following two formulas take INDEX one step further. Here, by omitting or using zero for the column argument, the inside INDEX returns the array for row 2: {"Coats",300}. And then the outside INDEX returns the first item from that array, "Coats". That is, these formulas use different methods to return exactly the same results as #1 and #2.

Formula 7:  =INDEX(INDEX(Data,2,),1)

Formula 8:  =INDEX(INDEX(Data,2,0),1)

As a practical matter, when you need to reference one row or column, I suggest that you always use the versions shown in formulas 6 and the inside version of 8. That is, if you want to specify an entire row or column, use a zero for the argument rather than omitting the argument.

One excellent reason for doing this is that when you specify a zero for the argument, you make it obvious what you intend to do. In contrast, if you omit the argument, particularly in Formula 7, your intentions aren't always obvious.

You can experiement with these formulas by downloading the workbook with these INDEX formulas here.

My next post in this series explains the MATCH function. And the last post will show you how to combine INDEX and MATCH.