How to do a VLookup in Excel


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)


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:

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.


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

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):

      AA.artist [Artist Name],
      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.