Lookup Formulas
How to Use INDEX-MATCH, Part 3: Six Lookups That VLOOKUP Can't Do
INDEX-MATCH formulas can perform many types of lookups that
VLOOKUP can’t. And they're faster. Here are six examples.
by Charley Kyd, MBA Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
|
In
Part 1 of this series, I explained the INDEX function. In
Part 2, I explained the MATCH function. And in this article, I’m
going complete this series by showing you how to combine INDEX
and MATCH to perform six simple lookups that VLOOKUP can’t do.
You can
download the
zip file of example workbooks for all three parts here.
So let’s get started…
Example 1: The lookup range is to the right of the value
range.
In
VLOOKUP formulas, the left-most column must be the lookup
range, and one or more value ranges must be to the right.
But INDEX-MATCH has no such limitation. Here, for example, the value range is at the left and the lookup range
is to the right. The example shows three formulas:
Position: =MATCH($E$1,Products,0)
The MATCH function in the Position cell works as a stand-alone lookup
function. It looks up "Bolts" from cell E1 and returns 2, telling us that
Bolts is the second product in the Products list.
ResultA: =INDEX(Codes,$E$3)
The INDEX function in the ResultA cell looks at the Codes list and
returns the value from the specified position. In this case, it
returns the code, X-45, from the second position.
ResultB:
=INDEX(Codes,MATCH($E$1,Products,0))
This INDEX-MATCH formula merely combines the formulas in
Position
and ResultA into one formula. To create this formula, I copied
the ResultA formula to ResultB, copied the formula text in the
Position cell,
and then pasted it into the ResultB formula,
replacing the "$E$3" that initially was there with the
Position formula.
That is, by combining a MATCH formula and an INDEX formula,
the ResultB formula becomes a self-contained INDEX-MATCH
formula.
Example 2: The lookup range is horizontal and the value
range is vertical.
If we use a lookup formula with "LOOKUP" in its name, we
can't mix horizontal and vertical lookups.
But INDEX-MATCH has no such limitation.Position: =MATCH($E$3,Products,0)
In the Position cell, the MATCH function looks up "Screws" in the
horizontal list in row 1, and tells us that Screws are in the
4th position.
ResultA: =INDEX(Codes,$E$5)
Here, the INDEX formula returns an item from the Codes list.
Specifically, it returns the fourth item, as specified by
Position.
ResultB: =INDEX(Codes,MATCH($E$3,Products,0))
As in Example 1, ResultB returns the merger of ResultA and
Position. Specifically, I merely replaced $E$5 (as it appears in
ResultA) with the Position formula.
Example 3: The lookup range is vertical and the value range
is horizontal.
As
in Example 2, we can't combine VLOOKUP and HLOOKUP in one
formula to return values from a combination of vertical and
horizontal lists.
This is just the reverse of Example 2. Here, the lookup range
is vertical and the value range is horizontal.
Position: =MATCH($E$3,Products,0)
MATCH finds the position of a lookup value (Nuts) in the
lookup range (Products).
ResultA: =INDEX(Codes,$E$5)
INDEX returns the value from a reference or array as
specified by a position number, which is 1 in this case.
ResultB:
=INDEX(Codes,MATCH($E$3,Products,0))
Again, ResultB combines the Position and ResultA formulas into
one formula, and returns its results.
Example 4: Sum the range between two INDEX-MATCH results.
Lookup
formulas return values, not cell references.
But because MATCH
can be used in combination with INDEX, INDEX-MATCH returns cell
references...which gives us greater power.
This example finds
the sum of any Values in the range from the Product1 value through Product2
value.
To illustrate, Product1 is Washers and its Value is in cell
B4. Product 2 is Saws and its Value is in cell B6. So the two
Result cells tell us that the sum of the range B4:B6 is 11.
Similarly, if Product2 were changed to Screws, the Result
cells would return 7...the sum of the Washers and Screws values.
Although this is an unusual use of INDEX-MATCH, there's
nothing unusual about the formulas that generate each result.
Position1: =MATCH($E$1,Products,0)
Position2: =MATCH($E$2,Products,0)
There's nothing unusual about these two formulas. They merely
return the positions of Product1 and Product2.
ResultA:
=SUM(INDEX(Values,$E$3):INDEX(Values,$E$4))
There's nothing REALLY unusual about the two INDEX functions in this
formula. The first INDEX function returns a reference to the
cell in the Values range as specified by cell E3, which is
Position1. And the second INDEX function returns a reference to
the Values cell specified by Position2.
Between the two INDEX functions you see a colon. This
colon serves the same purpose that the colon in a reference like
B4:B6 performs. It's known as a reference operator, and in this
formula, it
defines the range that begins with the first INDEX result (cell B4) and ends with
the second INDEX result (cell B6).
So just as we could enter a formula like =SUM(B4:B6), we can
enter a formula like the one in ResultA, which uses the two
INDEX functions to return the same references and give us the
same result as we would get if we entered =SUM(B4:B6) in a cell.
ResultB:
=SUM(INDEX(Values, MATCH($E$1,Products,0)):
INDEX(Values, MATCH($E$2,Products,0)))
This formula is longer than ResultA, but it does the same
thing. As in Examples 1-3, it merely replaces references to
cells that use MATCH formulas with the MATCH formulas
themselves.
Example 5: Perform a two-way lookup.
By
itself, VLOOKUP can't do a two-way lookup.
In this figure, for
example, VLOOKUP doesn't let us enter a product (which is in a column) and a
region (which is in a row) and find the value at the
intersection of the specified row and column.
But that's no problem for INDEX and MATCH.
G2: =MATCH($F$2,Products,0)
This cell finds the vertical position of Screws in the column of
Products. We learn that Screws is in position 4.
G3: =MATCH($F$3,Region,0)
This cell finds the horizontal position for South in the row of
Regions. We learn that South is in position 2.
ResultA: =INDEX(Values,$G$2,$G$3)
ResultA uses INDEX to return the value at the intersection of
the row specified by cell G2 and the column specified by cell
G3. The value, we see, is 99.
ResultB:
=INDEX(Values, MATCH($F$2,Products,0),MATCH($F$3,Region,0))
As before, this formula merely replaces the references to cells
that contain formulas using MATCH with the actual formulas that
those two cells contain.
Example 6: Look up a row and return multiple values from
it.
VLOOKUP
is two functions in one. It looks up a value and it returns the
result.
On the other hand, we can use MATCH to look up a value once and
then use several INDEX formulas that rely on that value to
return several different results.
By doing so, we get results much more quickly than we could with
VLOOKUP. This is because the INDEX function calculates VERY
quickly.
In the figure, for example, suppose we want to look up a product
and then return each of its values for the three regions shown.
VLOOKUP would need to look up the product each time. But not
INDEX-MATCH.
G2: =MATCH(F2,Products,0)
This formula performs the one lookup we need, and returns the row number
where the item is found.
G5: =INDEX(Values,$G$2,$F5)
G6: =INDEX(Values,$G$2,$F6)
G7: =INDEX(Values,$G$2,$F7)
Each of these formulas returns an item from a different Region
on the same row as the specified product.
In short, INDEX-MATCH is much more powerful than VLOOKUP, and
it's faster.
If you want to play with more example formulas from all three
articles about INDEX-MATCH, you can
download the
zip file of example workbooks for all three parts here.
|