Lookup functions allow you to extract data from an Excel table and use the resulting values somewhere else in Excel. However there are quite a variety of LOOKUP functions available and it's not always easy to decide which function to use. This article summarises the main LOOKUP functions available and describes how they are used.

The main LOOKUP functions are VLOOKUP, HLOOKUP, INDEX, MATCH and CHOOSE. We'll look at each in turn, starting with VLOOKUP. This tends to be the lookup function users learn about first.

VLOOKUP Function

VLOOKUP as its name implies uses a supplied value and looks up a specified table for a match to this supplied value in the first vertical column of the table. We state in the function which column number in this table to extract the data from, starting from the left hand side of the table. So the function looks something like =VLOOKUP(lookup value, table range, column number) and the extracted value will be displayed in the cell containing the VLOOKUP function.

The table we lookup must be sorted by the first column in ascending order for the function to work. If the table is not sorted by the first column, we add a fourth element in the function after the column number, FALSE. This tells the function to find an exact match in the first column even if the column is not sorted in order.

HLOOKUP Function

HLOOKUP works in a similar way to VLOOKUP except that the lookup value is used to find a match in the first row of the specified table. We state in the function which row number to extract the data from, starting from the top of the table.

So the function looks something like =HLOOKUP(lookup value, table range, row number) and the extracted value will be displayed in the cell containing the HLOOKUP function. If the top column of the table being looked up is not sorted in ascending order, right to left, we can again add a fourth element to the function after row number, so the revised function looks like =HLOOKUP(lookup value, table range, row number, FALSE) and this will make the function find an exact match for the lookup value in the top column of the table.

INDEX Function

The INDEX function takes VLOOKUP and HLOOKUP one step further, and allows us to find a match to the lookup value in ANY row or column in the specified table. We just need to tell the function which table to look in, and the row number and column number of the data to extract. So the function looks something like =INDEX(table range, row number, column number) and the function will then obligingly show the extracted data in the cell with the INDEX function.

So with INDEX we need to supply the row and columns numbers in the target table. To find an exact match rather than the nearest match using INDEX we add a fourth element 0 ( zero), so the function becomes =INDEX(table range, row number, column number,0). However suppose we want to be able to use a lookup vale to determine either the row number of column number or both. For this we need to use the MATCH function.

MATCH Function

The MATCH function will return the position of a lookup value in a row or column, rather than the actual value. So if the lookup value is a name and the specified cells in a row or column contains a list of names, MATCH will return the position number in this list of the lookup value name. Again, to specify an exact match for the lookup value, we add a 0 (zero) as a fourth element in the function, so it looks like =MATCH(lookup value, row or column range, 0).

We can combine lookup functions in different ways. For example suppose we want to use VLOOKUP with a lookup value to look for a match in the first column in a particular table, but we don't know which column number to use. We could use MATCH with different lookup value as one of the top row titles, and specify the range as the top row of the table. MATCH would then return the column number we wanted. We would then use the MATCH function within the VLOOKUP function to find the data to extract, and the combined formula would use two lookup values, one for VLOOKUP and the other for MATCH.

CHOOSE Function

The CHOOSE function is perhaps less widely use, but it can be very useful. The CHOOSE function lets you choose which item you want to use form a list of several items specified within the function. For example suppose you type this into an empty cell =CHOOSE(1,"Mon","Tues","Wed","Thur","Frid","Sat","Sun") and press Enter.

The cell will show the first value from the list, Mon. Edit the function and try different numbers before the list and you'll see that CHOOSE simply lets you select one item from the list. This can easier to use, for example, than using multiple nested Ifs in some situations, where the data list tends not to change. Another example might be for the list to contain a number followed by a list of formula, without the equals sign, but with the cell range in brackets after each formula name. Then you just change the number at the start of the list to apply a different function to a particular range of cells.

Has this article has whetted your appetite to find out more about Excel lookup functions? A really effective way is to attend a training course. This way you can really boost your Excel skills in a relatively short time.