Basic Lookup Formulas | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

You can use Excel's basic lookup functions to search a column or row for a lookup value to return another value as a result. Excel provides three basic lookup functions: HLOOKUP, VLOOKUP, and LOOKUP. The MATCH and INDEX functions are often used together to return a cell or relative cell reference for a lookup value.

On the CD

The examples in this section are available on the companion CD-ROM. The filename is basic lookup examples.xlsx.

The VLOOKUP Function

The VLOOKUP function looks up the value in the first column of the lookup table and returns the corresponding value in a specified table column. The lookup table is arranged vertically. The syntax for the VLOOKUP function is

 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The VLOOKUP function's arguments are as follows:

lookup_value: The value to be looked up in the first column of the lookup table.
table_array: The range that contains the lookup table.
col_index_num: The column number within the table from which the matching value is returned.
range_lookup: Optional. If TRUE or omitted, an approximate match is returned. (If an exact match is not found, the next largest value that is less than lookup_value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP cannot find an exact match, the function returns #N/A.

Note

If the range_lookup argument is TRUE or omitted, the first column of the lookup table must be in ascending order. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns #N/A. If the range_lookup argument is FALSE, the first column of the lookup table need not be in ascending order. If an exact match is not found, the function returns #N/A.

The classic example of a lookup formula involves an income tax rate schedule (see Figure 8-2). The tax rate schedule shows the income tax rates for various income levels. The following formula (in cell B3) returns the tax rate for the income in cell B2:

 =VLOOKUP(B2,D2:F7,3)

image from book
Figure 8-2: Using VLOOKUP to look up a tax rate.

The lookup table resides in a range that consists of three columns (D2:F7). Because the third argument for the VLOOKUP function is 3, the formula returns the corresponding value in the third column of the lookup table.

Note that an exact match is not required. If an exact match is not found in the first column of the lookup table, the VLOOKUP function uses the next largest value that is less than the lookup value. In other words, the function uses the row in which the value you want to look up is greater than or equal to the row value, but less than the value in the next row. In the case of a tax table, this is exactly what you want to happen.

The HLOOKUP Function

The HLOOKUP function works just like the VLOOKUP function except that the lookup table is arranged horizontally instead of vertically. The HLOOKUP function looks up the value in the first row of the lookup table and returns the corresponding value in a specified table row.

The syntax for the HLOOKUP function is

 HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

The HLOOKUP function's arguments are as follows:

lookup_value: The value to be looked up in the first row of the lookup table.
table_array: The range that contains the lookup table.
row_index_num: The row number within the table from which the matching value is returned.
range_lookup: Optional. If TRUE or omitted, an approximate match is returned. (If an exact match is not found, the next largest value less than lookup_value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP cannot find an exact match, the function returns #N/A.

Tip

If the lookup_value argument is text, it can include the wildcard characters * and ?. An asterisk matches any number of characters, and a question mark matches a single character.

Figure 8-3 shows the tax rate example with a horizontal lookup table (in the range E1:J3). The formula in cell B3 is

image from book
Figure 8-3: Using HLOOKUP to look up a tax rate.

 =HLOOKUP(B2,E1:J3,3)

The LOOKUP Function

The LOOKUP function has the following syntax:

 LOOKUP(lookup_value,lookup_vector,result_vector)

The function's arguments are as follows:

lookup_value: The value to be looked up in the lookup_vector.
lookup_vector: A single-column or single-row range that contains the values to be looked up. These values must be in ascending order.
result_vector: The single-column or single-row range that contains the values to be returned. It must be the same size as the lookup_vector.

The LOOKUP function looks in a one-row or one-column range (lookup_vector) for a value (lookup_value) and returns a value from the same position in a second one-row or one- column range (result_vector).

Caution

Values in the lookup_vector must be in ascending order. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns #N/A.

Note

The Help system also lists an "array" syntax for the LOOKUP function. This alternative syntax is included for compatibility with other spreadsheet products. In general, you can use the VLOOKUP or HLOOKUP functions rather than the array syntax.

Figure 8-4 shows the tax table again. This time, the formula in cell B3 uses the LOOKUP function to return the corresponding tax rate. The formula in B3 is

image from book
Figure 8-4: Using LOOKUP to look up a tax rate.

 =LOOKUP(B2,D2:D7,G4:G9)

Caution

If the values in the first column are not arranged in ascending order, the LOOKUP function may return an incorrect value.

Note that LOOKUP (as opposed to VLOOKUP) can return a value that's on a different row than the matched value. If your lookup_vector and your result_vector are not part of the same table, LOOKUP can be a useful function. If, however, they are part of the same table, VLOOKUP is usually a better choice if for no other reason than that LOOKUP will not work on unsorted data.

Combining the MATCH and INDEX Functions

The MATCH and INDEX functions are often used together to perform lookups. The MATCH function returns the relative position of a cell in a range that matches a specified value. The syntax for MATCH is

 MATCH(lookup_value,lookup_array,match_type)

The MATCH function's arguments are as follows:

lookup_value: The value you want to match in lookup_array. If match_type is 0 and the lookup_value is text, this argument can include the wildcard characters * and ?.
lookup_array: The range being searched.
match_type: An integer (−1, 0, or 1) that specifies how the match is determined.

Note

If match_type is 1, MATCH finds the largest value less than or equal to lookup_value. (lookup_array must be in ascending order.) If match_type is 0, MATCH finds the first value exactly equal to lookup_value. If match_type is −1, MATCH finds the smallest value greater than or equal to lookup_value (lookup_array must be in descending order). If you omit the match_type argument, this argument is assumed to be 1.

The INDEX function returns a cell from a range. The syntax for the INDEX function is

 INDEX(array,row_num,column_num)

The INDEX function's arguments are as follows:

array: A range
row_num: A row number within the array argument
column_num: A column number within the array argument

Note

If array contains only one row or column, the corresponding row_num or column_num argument is optional.

Figure 8-5 shows a worksheet with dates, day names, and amounts in columns D, E, and F. When you enter a date in cell B1, the following formula (in cell B2) searches the dates in column D and returns the corresponding amount from column F. The formula in B2 is

image from book
Figure 8-5: Using the INDEX and MATCH functions to perform a lookup.

 =INDEX(F2:F21,MATCH(B1,D2:D21,0))

To understand how this works, start with the MATCH function. This function searches the range D2:D21 for the date in cell B1. It returns the relative row number where the date is found. This value is then used as the second argument for the INDEX function. The result is the corresponding value in F2:F21.