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.

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.

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.