VLOOKUP Function

If you are using nested IF statements, which contain several IFs, it might be worth considering using the VLOOKUP function instead.

LOOKUP functions can be used to lookup text, numeric or logical values from a list and return data corresponding to the value looked up.

The ‘VLOOKUP’ function performs a ‘vertical’ lookup. That is, it will search for a value in the first column of a table and return a value from whichever column you specify (along the found row).

 

The basic syntax of the VLOOKUP function is as follows:

=VLOOKUP(<lookup value>, <table array>,<column index no>, <range lookup>)

 

Lookup Value: The value you are searching the first column of your list/table for

Table Array: A reference to the location of the Lookup list/table

Column Index No: The number of the column in the list/table from which to return a value

Range Lookup: The logical value of TRUE or FALSE (or 1 or 0) which indicates whether or not you are looking for an exact match from the list. False indicates that you are looking for an exact match.

 

Using VLOOKUP to search in Bands (Default)

Excel will look at a list used in a VLOOKUP as a numeric or alphabetical range. If the VLOOKUP was searching for the number ‘75’ in the table below, it will search in bands by default, ie it will look between numbers to return a value for the number. It will therefore look in the band between 50 and 100 and return the value 5%. The default is TRUE (ie look in bands)

0 0%
50 5%
100 7%
150 10%

However using the same principal and using the example below, if we were searching for the word ‘Foggy’, the lookup would not find it in the list, but assume that we wanted the nearest match between D (Dry) and R (Rain). The value returned would therefore be ‘Walk’.

Cloudy Bike
Dry Walk
Rain Drive
Snow Ski

 

Using VLOOKUP to find an Exact Match

The default of TRUE therefore does not apply in this case. The VLOOKUP statement you use if you are looking for an exact match in your list is slightly different to the one you use if you are looking for the nearest match.

If you are searching a list for an exact match, you will need to include the ‘FALSE’ as a range lookup within the formula.

 

HLOOKUP Function

The ‘HLOOKUP’ function performs a ‘horizontal’ lookup. That is, it will search for a value in the first row of a table and return a value from whichever row you specify (in the found column).

 

The basic syntax of the HLOOKUP function is as follows:

=HLOOKUP(<lookup value>, <table array>,<row index no>, <range lookup>)

 

Lookup Value: The value you are searching the first row of your list/table for

Table Array: A reference to the location of the Lookup list/table

Row Index No: The number of the row in the list/table from which to return a value

Range Lookup: The logical value of TRUE or FALSE (or 1 or 0) which indicates whether or not you are looking for an exact match from the list. False indicates that you are looking for an exact match.

 

Applies to: Microsoft Excel 2007, Excel 2003, Excel 2002, Excel XP, Excel 2000, Excel 97