Excel Function : Look-up Formula

What Is a Lookup Formula?

A lookup formula essentially returns a value from a table (in a range) by looking up another value. A common telephone directory provides a good analogy: If you want to find a person’s telephone number, you first locate the name (look it up) and then retrieve the corresponding number.

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 that you want to look 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 used.) If FALSE, VLOOKUP searches for an exact match. If VLOOKUP cannot find an exact match, the function returns #N/A.

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.

If the lookup_value argument is text (and the fourth argument, range_lookup, is

FALSE), you can include the wildcard characters * and ?. An asterisk matches any group of characters, and a question mark matches any single character.

The classic example of a lookup formula involves an income tax rate schedule (see Figure 1).

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)

 

Figure 1: 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 that you want to look 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 used.) If FALSE, VLOOKUP searches for an exact match. If VLOOKUP cannot find an exact match, the function returns #N/A.

If the lookup_value argument is text (and the fourth argument is FALSE), you can use

the wildcard characters * and ?. An asterisk matches any number of characters, and a

question mark matches a single character.

Figure 2 shows the tax rate example with a horizontal lookup table (in the range E1:J3).

The formula in cell B3 is =HLOOKUP(B2,E1:J3,3)

Figure 2: Using HLOOKUP to look up a tax rate.

 sources : Microsoft Excel -2010 formulas Book