For business users of Microsoft Excel Free guides and templates

Excel Lookup Functions

Excel’s VLOOKUP vs INDEX-MATCH Functions

Most Excel users need to look up data in workbooks. But what's the best lookup method?


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Excel offers two primary lookup methods: VLOOKUP and INDEX-MATCH. Although the two methods are similar, INDEX-MATCH is more powerful.

(You can download the workbook with the following examples here.)

I suspect, however, that VLOOKUP is better-known and more widely used. Probably, this is because if you want to look up something it makes sense to use a function with "lookup" in its name.

In this article I'll explain both VLOOKUP and INDEX-MATCH. And along the way, I’ll explain the advantages and disadvantages of these two methods.

There are, of course, two additional lookup functions:

HLOOKUP works just like VLOOKUP, but horizontally rather than vertically. So everything I say about VLOOKUP also applies to HLOOKUP.

LOOKUP was designed for compatibility with Lotus 1-2-3; and 1-2-3’s @LOOKUP function was designed for compatibility with VisiCalc. So LOOKUP is less powerful than VLOOKUP. However, it does have one advantage over VLOOKUP, which I’ll in discuss in a later article in this series.

The VLOOKUP function has these arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

The range_lookup argument is optional, often overlooked, but very useful. This is because range_lookup determines whether VLOOKUP returns an approximate match or an exact match.

Although Excel actually uses a different technique, here’s a simple way to think about how approximate matches work:

Excel works its way down the first column until it finds a value greater than the lookup value. Then it backs up one row to return its results. If the lookup value is greater than the last number in the column, it delivers that last result.

When Should You Use an Approximate Match?

The only time you should use an approximate match is when you DEFINITELY need an approximate match. If you need an exact match, and if you want accurate results, NEVER use an approximate match.

By default, VLOOKUP returns an approximate match. This is unfortunate, for two reasons. First, in my experience, most Excel users need exact matches most of the time. Second, approximate matches are risky, because they can return false results.

This figure illustrates a typical occasion when approximate matches are needed.

The figure shows a schedule of quantity discounts. If a purchase is between 1 and 4 units, no discount is offered. A purchase from 5 through 24 units gets a 5% discount, and so on. And any purchase of 200 or more units gets a 20% discount.

The approximate-match feature allows us to look up the discount for an order of any size. Cell E4, for example, shows that the discount for a purchase of 7 units is 5%.

Here’s the formula for this calculation:

E4: =VLOOKUP(E$3,$A$3:$B$7,2,TRUE)

(The last argument is the optional range_lookup value. It could have been omitted, because the default value for this argument is TRUE.)

The formula in cell E7 in the figure above is similar. It shows that VLOOKUP works as you expect it to for large values. That is, any purchase in excess of the largest quantity will get the largest quantity discount.

Notice in the figure that the units are sorted in ascending order. This is an ABSOLUTE requirement. If your data isn’t sorted, you can get false results.

How to Get False Results from Excel Lookup Functions

Your data MUST be sorted when you use approximate matches. This is true for all Excel lookup functions that return approximate matches.

The reason for this requirement is that Excel doesn’t just work its way down a column or across a row for approximate matches. Instead, Excel uses a binary search technique. That is, Excel checks the middle of the list. If that value is less than the lookup value, it jumps to the middle of the following section; if the value is less, it jumps to the middle of the preceding section. It continues to halve the distance until it finds the correct result.

This technique is much faster than searching down a row or column, but it can produce unexpected results if the data isn’t sorted.

Often, if you do an approximate match against unsorted data, an Excel lookup function will return an error value. That’s the best result because it alerts you to a problem.

Approximate match, unsorted dataBut unfortunately, when Excel performs an approximate match against unsorted data, it can produce false results. For example, this figure illustrates two types of incorrect results.

The SKU #1 example searches for a value that’s not in the table. The two formulas in the example are:

F3:  =VLOOKUP(F$2,$A$4:$C$8,2,TRUE)

F4:  =VLOOKUP(F$2,$A$4:$C$8,3,TRUE)

The VLOOKUP function has these arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Because the range_lookup value is TRUE in cells F3 and F4, these formulas are looking for approximate matches.

As you can see in the figure, the two formulas return results for the SKU code A101 instead. This is a significant problem, because Excel returns a false result, and that’s dangerous.

The SKU #2 example uses similar formulas to search for a value that IS in the table. But because the SKUs column isn't sorted, it also returns values for the SKU code A101 instead of the correct result.

So the two main conclusions are obvious:

1. Use approximate matches only when you have a good reason to do so.

2. Be CERTAIN your data is sorted when you use approximate matches.

How to Use Exact Matches with Excel’s VLOOKUP and HLOOKUP Functions

The default behavior of VLOOKUP and HLOOKUP is to return an approximate match.

To understand why Microsoft probably made this choice, consider that Excel’s four lookup functions (LOOKUP, VLOOKUP, HLOOKUP, and MATCH) were written at least 20 years ago. Back then, computers were much slower than they are today. Back then, using an exact-match search caused calculation to slow noticeably.

Therefore, the Excel team set up the fastest method – the approximate match method – as the default search method.

But today, for most purposes, there’s no apparent difference in calculation time between an exact and an approximate match. Today, therefore, Excel’s default search method is unfortunate, for at least two reasons. First, Excel users typically need exact matches more frequently than they need approximate matches. Second, approximate matches can produce false results.

Let’s see how exact matches work with typical data…

The formulas in cells F2 and F3 return correct results. And because the formulas in cells F6 and F7 look up data that doesn't exist, they also return correct results.

Two examples that show correct use of the VLOOKUP formula.

Columns A-C show a simple database of SKUs (Stock Keeping Units) with their descriptions and prices.

Column F shows two sets of examples.

Notice that the SKU data in column A isn’t sorted. When you use exact matches, the sort order doesn’t matter.

The SKU #1 example searches for information about the SKU code B19, using these formulas:

F2:  =VLOOKUP(F$1,$A$3:$C$7,2,FALSE)

F3:  =VLOOKUP(F$1,$A$3:$C$7,3,FALSE)

Remember that the VLOOKUP function has these arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Because the range_lookup value is FALSE in these formulas, they are looking for exact matches. And because the SKU code B19 exists in the table, the formulas find and return the correct results.

That’s GREAT news…We got a CORRECT result!

The SKU #2 example offers even better news. Here are its formulas:

F6:  =VLOOKUP(F$5,$A$3:$C$7,2,FALSE)

F7:  =VLOOKUP(F$5,$A$3:$C$7,3,FALSE)

Here, we get error values when we search for data that’s not in the list. That’s even better news, because we’re not getting false data. That is, when we use only exact matches we can rely on getting an error value if we search for data that doesn’t exist.

The Limitations of VLOOKUP and HLOOKUP

The VLOOKUP formula uses this syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Let’s use this function to look up some values from this little database.

To look up “G23″ in the SKUs column and return its description, we would use:

=VLOOKUP("G23",$A$3:$C$7,2,FALSE)

To look up “G23″ in SKUs and return its price, we would use:

=VLOOKUP("G23",$A$3:$C$7,3,FALSE)

To look up “Coats” in the Items column and return its price, we would use:

=VLOOKUP("Coats",$B$3:$C$7,2,FALSE)

That’s fine. But how can we use VLOOKUP to look up “Shoes” in the Items column and return its SKU?

We can’t.

The reason is simple. VLOOKUP only can look up values in the first column of the table_array, and we only can return values from within the table array. So if we want to look up a value in one column and return values to the left of that column, we’re out of luck.

(No, it wouldn’t work if we try to use a negative value for the col_index_num.)

To return the value we need, you would think that we could use the LOOKUP function, which has this format:

=LOOKUP(lookup_value, lookup_vector, result_vector)

The problem is that LOOKUP’s lookup_vector column MUST be sorted in ascending order. So if our database were sorted by the Item column we could return its SKU, which is something that VLOOKUP can’t do.

But because the data isn’t sorted, we’re out of luck…at least for LOOKUP, VLOOKUP, and HLOOKUP.

Unfortunately, this isn’t the only limitation of these three functions. The limitations are why I haven’t used these three functions since about 1990. Instead, I only use INDEX-MATCH.

Excel’s Best Lookup Method: INDEX-MATCH

The most powerful and flexible way to look up data in Excel is the INDEX-MATCH method. It relies on two Excel functions:

=INDEX(reference, row_num, column_num)

  • reference—a range of cells
  • row_num—the row in reference from which to return data.
  • column_num—the column in reference from which to return data.

If reference is one row or column, the INDEX function can use this syntax:=INDEX(reference, cell_num)

=MATCH(lookup_value, lookup_array, match_type)

  • lookup_value—The value to match in lookup_array.
  • lookup_array—A range of cells with data.
  • match_type—Specifies how Excel matches the lookup_value with values in the lookup_array. For exact matches, always use 0 for this argument.

The figure below shows the INDEX-MATCH method in action…

B11:  =MATCH($A11,$A$3:$A$7,0)

This formula tells us that the text “G23” is found in the third row of the SKUs column. And the formula copied to B12 says that “A101” is found in the second row.

Notice that because we want an exact match, the last argument in cell B11 has a value of zero.

C11:  =INDEX($B$3:$B$7,$B11)

This formula for cell C11 returns the item for the cell number specified by the value in cell B11.

D11:  =INDEX($C$3:$C$7,$B11)

And this formula for cell D11 returns the price for the cell number specified in cell B11.

Of course, we could have combined both the INDEX and MATCH into one formula, as shown in cell E11:

E11:  =INDEX($C$3:$C$7,
MATCH($A11,$A$3:$A$7,0))

This combination works just like the VLOOKUP function in cell F11:

F11:  =VLOOKUP($A11,$A$3:$C$7,3,FALSE)

So far, INDEX-MATCH and VLOOKUP have the same capabilities. But now consider the formulas in cells B16 and C16:

B16:  =MATCH($A16,$B$3:$B$7,0)

C16:  =INDEX($A$3:$A$7,$B16)

In these two formulas, we look up “Ties” in the column of Items and return its SKU from the column of SKUs. This is a result that VLOOKUP can’t produce, as I explained above.

More commonly, we combine these formulas into one:

E16:  =INDEX($C$3:$C$7,MATCH($A16,$B$3:$B$7,0))

The INDEX-MATCH method has many additional advantages that you’ll discover as you use this method.

If you aren’t using INDEX-MATCH for your lookups, you’re missing a lot of power. Give it a try.

Take Your Next Steps

You can take the next step with INDEX-MATCH in two ways. First, you can download the completed workbook here.

Second, if you want to add advanced lookups to your normal reports and analyses, I can help you in three ways. To learn more, see Excel Training, Coaching, and Consulting.


Tags: #excel, #vlookup, #index, #match,#index-match, #lookup

How to Fight Spreadsheet Hell with Three Excel Lookup Functions  

Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel

Free Excel Dashboards


Charley's SwipeFile charts