The `HLOOKUP()`

and `VLOOKUP()`

functions are two of the most commonly used functions in Excel, but there are a number of pitfalls that should be avoided.

Both functions take either three or four arguments (the fourth is optional):

`lookup_value`

the value that you are trying to match.`table_array`

the range in which you are trying to look up the value. The function will try to match your`lookup_value`

in the leftmost column (`VLOOKUP`

) or topmost row (`HLOOKUP`

) of this range.`col_index_num`

(`VLOOKUP`

) or`row_index_num`

(`HLOOKUP`

) the column or row containing the corresponding values you want to find. The numbering starts at 1 for the column or row containing the values to be matched.`range_lookup`

whether to perform an exact or approximate lookup. If this is`TRUE`

you’ll get an approximate lookup, if it is`FALSE`

you’ll get an exact lookup. This arrgument is optional: if no value is specified, it will be assumed to be`TRUE`

.

When should you use an approximate lookup, and when an exact lookup? It’s easier to decide if you understand what is going on as the lookup is performed.

## Approximate lookups

If you are doing an approximate lookup, Excel will take each cell in the leftmost column (`VLOOKUP`

) or topmost row (`HLOOKUP`

) in turn, and see how it compares to the value it is looking for (`lookup_value`

). If the value of the current cell is the same as the `lookup_value`

, it stops, having found the correct row (`VLOOKUP`

) or column (`HLOOKUP`

). If the value of the current cell is less than the `lookup_value`

, it moves on to the next cell. If the value of the current cell is greater than the `lookup_value`

, it stops, and uses the previous row (`VLOOKUP`

) or column (`HLOOKUP`

). In other words, Excel returns the **next largest** value, as long as **the lookup range is sorted in ascending order**.

This provides a way of remembering the significance of the optional argument: use `TRUE`

when the lookup range is sorted, and `FALSE`

when it is not sorted.

Using `TRUE`

when the lookup range is not sorted will lead to unpredictable results: for `VLOOKUP`

, the row that will be used to provide the value will be the row above the first row whose leftmost column has a value greater than `lookup_value`

.

If the `lookup_value`

is smaller than the first value in the leftmost column (`VLOOKUP`

) or topmost row (`HLOOKUP`

), Excel will return the `#N/A`

value.

## Exact lookups

When Excel performs an exact lookup, it will take each cell in the leftmost column (`VLOOKUP`

) or topmost row (`HLOOKUP`

) in turn, and see how it compares to the value it is looking for (`lookup_value`

). If the value of the current cell is the same as the `lookup_value`

, it stops, having found the correct row (`VLOOKUP`

) or column (`HLOOKUP`

). If it gets to the end of the column (`VLOOKUP`

) or row (`HLOOKUP`

) without finding a match, it will return the `#N/A`

value.

## Approximate or exact?

You should always specify the optional fourth argument to these functions, so that it is obvious which sort of lookup you intend.

Whether you use an approximate or exact lookup will, of course, depend on the use to which you are going to put the values that you are looking up. However, there are some situations that you should avoid:

- Don’t use an approximate lookup if the lookup table isn’t sorted in ascending order by the values in the leftmost column (
`VLOOKUP`

) or topmost row (`HLOOKUP`

). - Don’t use an exact lookup if your lookup values are floating point numbers (ie, any numbers with decimal points). This is because floating point numbers have to be exactly the same in order to match:
`4.9999999999999999`

won’t match`5`

. Remember that even if a number is displayed as`5.0`

, it may actually be`4.9999999999999999`

. - Be very careful when using floating point numbers even with approximate lookups.
`4.9999999999999999`

is less than`5`

, so Excel wouldn’t find the correct row even with an approximate lookup.

## Calculation speed

Lookups can take a long time to compute, if the lookup table is large or if many lookups are performed. Approximate lookups are faster, as Excel doesn’t always have to go through every row or column in the lookup table. It is often possible to use an approximate lookup, if the lookup table is sorted and is known to contain all possible values.

Many people use a formula such as `=IF(ISNA(VLOOKUP(Currency,Rates,2,FALSE)),0,VLOOKUP(Currency,Rates,2,FALSE))`

. Because of the way Excel works, this **always** does both lookups in the formula, thus doubling the calculation time. It is much more efficient to have a column (or row) to contain the raw lookup results, and then test them with the `ISNA()`

separately.