Look up values with VLOOKUP, INDEX, or MATCH

To find a value in a large list, you can use a lookup function. The VLOOKUP function is widely used, but the HLOOKUP function and using the INDEX and MATCH functions together can work well for you too.

Here's the scoop on VLOOKUP and the arguments it uses:

=VLOOKUP(<search value>,<lookup range>,<column>,<Approximate match>)

For example; =VLOOKUP(21500,C2:E7,3,FALSE)

• The first argument—a piece the function needs to work—is the value you want to search on. That can be a cell reference like B2, or a value such as "smith" or 21500.

• The second argument is the range of cells you think contains the value you want to find.

  Important: With VLOOKUP, the column containing the value or cell reference you search on needs to be the leftmost column in the range.

• The third argument is the column in the lookup range of cells that contains the value you want to see.

Although the fourth argument is optional, most people enter FALSE (or 0). Why? Because this forces the function to find an exact match on the search. You can enter no argument, or TRUE, but if an exact match isn't found, the function returns the closestapproximate match—and usually, most people don't want an approximate match.

To show how an approximate match can be a serious problem, say you're looking for the price for a part with ID 2345768, but you switch two numbers and mistype it in the formula like this: =VLOOKUP(2345678,A1:E7,5). The formula returns the price for the wrong part because VLOOKUP found the closest number less than or equal to the number you specified (2345678). You could end up billing a customer incorrectly because of this mistake.

If you specify FALSE or 0 for the Approximate Match argument and there's no exact match, the formula returns #N/A in the cell instead of the wrong value—a much better scenario. In this case, #N/A doesn't mean you entered your formula wrong (except for the mistyped number); it means that that 2345678 wasn't found—you wanted 2345768.

This example shows an example of how the function works. When you enter a value in cell B2 (the first argument), VLOOKUP searches cells C2:E7 (the second argument) and returns the closest approximate match from the third column in the range, column E (the third argument).

In this example, the fourth argument was left blank, so the function returns an approximate match.

Using the HLOOKUP function

Once you're comfortable with VLOOKUP, the HLOOKUP function isn't hard to use. You enter the same arguments, but HLOOKUP finds values in rows instead of columns.

Using INDEX and MATCH together

When you don't want to be constrained to searching on the leftmost column, you can use a combination of the INDEX and MATCH functions. A formula that uses these together is a little more complex than a formula with a VLOOKUP function, but it can be more powerful—and there are people out there who strongly prefer the INDEX/MATCH combination over the VLOOKUP function.

This example shows a small list where the value we want to search on, Chicago, isn't in the leftmost column. So, we can't use VLOOKUP. Instead, we'll use the MATCH function to find Chicago in the range B1:B11. It's found in row 4. Then, INDEX uses that value as the lookup argument, and finds the population for Chicago in the 4th column (column D). The formula used is shown in cell A14.

More about lookup functions

• Quick Reference Card: VLOOKUP refresher

• Lookup and reference functions (reference)

• Use the table_array argument in a VLOOKUP function

Top of Page