Arguably the most helpful function Excel has to offer. Vlookup allows you to find any value you require within a table, providing your data is organised in vertical lists (the column headers are in the first row of the table).
The Vlookup tool allows you to search for a lookup field in one column and find its corresponding value on the same row, a column index then identifies where in that row the value will be “looked up” from.
The standard syntax looks something like this:
=VLOOKUP(Lookup Value , Table, Search Column, Exact Match or Approximate Match)
For example if we wanted to lookup the Cost value from Worksheet2 and place it into Worksheet1, you would use the vlookup formula.
To get this formula started we need:
- A unique identifier for each row. The hardest part of the formula is working out what uniquely identifies the data row you are searching for. In the this case it is the SalesID, which identifies the cost of a sale in both worksheet1 and worksheet2.
- A range of cells/table to search within. You can use a range such as $A$2:$D$12 (the $ makes the row or column static, so if you drag the formula your range will not change).
However if your table is dynamic and your list size is going to change, you are better off using columns to define your table. Providing you do not have separate tables below the current table. So the range in this example could be defined as $C:$E or $C$2:$E$12.
- A column index indicating which column within our table we would like to retrieve our value from, which must be within the limit of our range. C-E is our column range which only provides us with 3 columns, Cost is the third column so this variable will be set to 3.
- You can optionally include a true or false as your forth variable, this indicates whether you are searching for a partial match or an exact match. If you decide to use * wild cards in your search criteria, you should use partial match, otherwise using false for an exact match is advisable.