Lookups: 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).vlookup 20160801
  • 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.
%d bloggers like this: