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.
by
Charley Kyd, MBA Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
|
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:
- It’s never slower than VLOOKUP and can be much faster.
- It returns a reference rather than a value, which allows
us to use it to do more things in our formulas.
- It doesn’t care where the result array is with regard to
the lookup array...even in a different workbook.
- It can return approximate matches from data sorted
largest to smallest.
- 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.
|