The goal when using VLookup is to join 2 different tables using a common column that we call a Key.
In the example below we want to join the age and net worth of different artists.
The general VLookup formula goes like this:
=VLookup(Key from first table, Search this Key in the second table, Return the value of column # in second table, 0)
VLookup is one of the most popular and used function in Excel. It often comes up in interview tests and can greatly increase your productivity.
You can think of a VLookup as an operation represented by the Venn diagram below.
You’re keeping all the data from table 1 and merging it with the intersection of table 1 and table 2. That means you will not always have all the data from table 2 in your final table.
One case of this is when your key column in table 1 has less values than your key column in table 2:
If you’re familiar with SQL, VLookup in Excel is the same as a LEFT JOIN.
One thing to keep in mind when doing a VLookup is that the Key always need to be the first column.
Let’s take a look at the different arguments of the VLookup function:
VLookup = (lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: A single cell value that you will search for in the first column of the table_array.
- table_array: A range (one to multiple rows and columns) that includes the key column (first column) and following columns.
- col_index_num: The column # that contains the information you need. The first column (key column) is 1.
- [range_lookup]: This argument can be 0 or 1. 0 means an exact match and 1 will return the closest value. For example, 1 would be used when doing a VLookup with an interval. Most of the time you will use 0.
When you filled out the VLookup function it will look like this:
=vlookup(L6,$L$6:$J$15, 2, 0)
This means search the value in cell L6 in range $L$6:$J$15 and return the value in the second column for an exact match.
Notice that the range contains the sign “$”, which means that when you drag down the VLookup formula it won’t automatically redefine your range.
When the formula returns the correct value, double-click the lower right corner of the cell containing the formula to drag it down automatically:
You can download the Excel workbook to practice: