How to do a VLookup in Excel

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.

how to do a vlookup in excel

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.

venn

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:

vlookup 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])

  1. lookup_value: A single cell value that you will search for in the first column of the table_array.
  2. table_array: A range (one to multiple rows and columns) that includes the key column (first column) and following columns.
  3. col_index_num: The column # that contains the information you need. The first column (key column) is 1.
  4. [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:

vlookup 3

You can download the Excel workbook to practice:

VLookup Practice

Get The Job You Want – Free Content

2 thoughts on “How to do a VLookup in Excel”

  1. Sandeep Kothari

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

    1. Hi 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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.