Lookups in Excel

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):

1. lookup_value the value that you are trying to match.
2. 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.
3. 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.
4. 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 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.