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.
Excel Monkey 