In order to do a vertical lookup in Excel many users usually
use VLOOKUP function. It’s understandable because VLOOKUP means Vertical Lookup
J. And if you are
offered to use INDEX MATCH functions instead of VLOOKUP you can ask "What
do I need that for?". The point is that VLOOKUP is not the only lookup
formula available in Excel, and its limitations to prevent you from getting the
desired result in many situations. Excel's INDEX MATCH is more flexible and has
certain features that make it superior to VLOOKUP in many respects.
Initially we consider INDEX
& MATCH functions
separately to understand how they work and then we’ll use them together
to understand their key strengths. We will find examples that will help you
easily solve complex tasks. Another benefit of using of INDEX & MATCH
functions is that instead of just a vertical lookup, INDEX MATCH allows you to perform a matrix
lookup, or “two-way lookup”. This combination formula may initially seem
complex (because of its three individual formulas), but after you understand how
they work, using of them will help you in many situations.
The INDEX function has
the following arguments:
array:
A range
row_num:
A row number within the array argument
column_num: A column number within the array
argument
If both row_num and column_num parameters are used, the
INDEX function returns the value in the cell at the intersection of the
specified row and column. For
example, looking at the table below in the range A5:E55 we can use INDEX to return the capital of
California with a formula as follows: =INDEX(A5:E55,6,2)
The result returned is Sacramento.
|
On its own the INDEX function is pretty inflexible because you
have to hard key the row and column number, and that’s why it works better with
the MATCH function.
The MATCH function has
three arguments:
- lookup_value: The value
(that you want to match in lookup_array).
If match_type
is 0 and the lookup_value is text, this argument can include the wildcard
characters * and ?.
- lookup_array: The range
(that you want to search). This should be a one-column or one row range.
- [match_type]: An integer
(–1, 0, or 1) that specifies how the match is determined.
1 - find the largest value less than or equal to lookup_value
(the list must be in
ascending order)
0 - find the first value exactly equal to lookup_value. Lookup_array
(the list can be in
any order)
-1 -- find the smallest value greater than or equal to lookup_value.
(the list must be in
descending order)
Note: If match_type is omitted, it is assumed to be 1.
Now we know the basics of these two functions, and start to use MATCH
and
INDEX together. Below is the syntax for using this
formula combination.
= INDEX ( array , MATCH ( lookup_value , lookup_array , 0 ) , MATCH (lookup_value , lookup_array , 0 ) )
And now, let us apply this formula in practice. Below, there
is a list of the most populated counties in the world. Suppose, we want to know
the number of population in the Japan in the year 2008:
OK, let's start on the formula. It’s a good practice to create a
complex Excel formula with one or several nested functions. So let’s write each
individual function first. Start by writing two MATCH functions that will
return the row and column numbers for your INDEX function.
Vertical match - you search through column B, (cells B3
to B12), for the value in cell H2 ("Japan"): MATCH($H$2,$B$3:$B$12,0).
This MATCH formula returns 10 because "Japan" is the 10th
item in range $B$3:$B$12
Horizontal match - you search for the value in cell H3
("2008") in row 2,
MATCH($H$3,$A$2:$E$2,0).
Now, put the above formulas inside the INDEX function:
=INDEX($A$3:$E$12,
MATCH($H$2,$B$3:$B$12,0), MATCH($H$3,$A$2:$E$2,0)) and
the result will be 128.
If we replace the MATCH functions with the returned numbers, the
formula is much easier to understand: = INDEX($A$3:$E$12,
4, 10, 0))
Meaning, it returns a value at the intersection of the 4th row and 10th column in range A3:E12, which is the
value in cell D12.
That's all!
