Lookup Formulas
How to Use INDEXMATCH, Part 3: Six Lookups That VLOOKUP Can't Do
INDEXMATCH 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, 20052014
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 leftmost column must be the lookup
range, and one or more value ranges must be to the right.
But INDEXMATCH 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 standalone 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, X45, from the second position.
ResultB:
=INDEX(Codes,MATCH($E$1,Products,0))
This INDEXMATCH 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 selfcontained INDEXMATCH
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 INDEXMATCH 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 INDEXMATCH results.
Lookup
formulas return values, not cell references.
But because MATCH
can be used in combination with INDEX, INDEXMATCH 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 INDEXMATCH, 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 13, it merely replaces references to
cells that use MATCH formulas with the MATCH formulas
themselves.
Example 5: Perform a twoway lookup.
By
itself, VLOOKUP can't do a twoway 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
INDEXMATCH.
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, INDEXMATCH is much more powerful than VLOOKUP, and
it's faster.
If you want to play with more example formulas from all three
articles about INDEXMATCH, you can
download the
zip file of example workbooks for all three parts here.
