Microsoft Office Tutorials and References

In Depth Information

**Understanding lookup and reference functions**

You can use the VLOOKUP function to retrieve information from the table in Figure 14-2.

Figure 14-2
VLOOKUP returns a value in the same row as the lookup value.

Youâ€™ll find the Lookup.xlsx file with the other examples on the companion website.

Remember that these lookup functions usually search for the greatest comparison value

that is less than or equal to the lookup value, not for an exact match between the

comparison values and the lookup value. If all the comparison values in the first row or column

of the table range are greater than the lookup value, the function returns the #N/A error

value. If all the comparison values are less than the lookup value, however, the function

returns the value that corresponds to the last (largest) comparison value in the table, which

might not be what you want. If you require an exact match, type
FALSE
as the
range_

lookup
argument.

The worksheet in Figure 14-3 shows an example of a horizontal lookup table using the

HLOOKUP function.

Figure 14-3
HLOOKUP returns a value in the same column as the lookup value.