## Summary

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)

## In-Depth

**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.

### Theory

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.

### Practice

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:

Sandeep KothariDear Alex, In the case of the first set of tables, vlookup does not do a left join but a full outer join. Pl check & revert.

Coach ManciniHi Sandeep and thanks for your comment.

executing the query below with a left join returned the desired result (full outer join returns the same in that case):

SELECT

AA.artist [Artist Name],

Age,

Worth [Net Worth]

from coachmancini.artistAge AA

LEFT JOIN coachmancini.artistWorth AW

ON AA.artist = AW.artist