Lookups: VLOOKUP()

Vlookup()

VLOOKUP() extracts an item from a list based on a value in a specific cell. In excel speak:

• The value in the specific cell is the lookup_value
• The list is a Table_array and normally has several columns
• The item returned comes from a specific column in the table array (NB if you specify a column number greater than the number of columns in the data then this will produce a #Ref! error).
• NB there is a fourth part of the formula which tells it whether you want an exact match or the closest match in ascending order

The formula is as follows =Vlookup(lookup value, data list, column, match type).  The formula tells excel to go down a single column list until it finds the value it is looking for and then to go across a specific number of columns to get the result required

The example below illustrates some of the points:

• There is a download from the pay system in cells H7 to L28 giving details of name, pay number, date of birth, age and salary (5 columns in total).
• In Cells A1 to F14 there is a summary of a particular employees details
• The summary will vary depending on what is entered onto cell D4. NB this entry is controlled with a data validation (list type based on the names in cells H8 to H28)
• Exact matches are required so the last part of the formula must be zero ‘0’)
• This give the full formula to extract the pay number from column 2 of the data as =Vlookup(\$D\$4,\$H\$8:SL\$28,2, 0).
• Once the formula is created in cellD6 (with the appropriate \$ signs to fix rows and column references) then this can be copied to Cells D8, D10 and D12 with the column number being changed to 3, 4, or 5 respectively.