MrExcel.com - Your One Stop for Excel Tips & Solutions

Use VLOOKUP to Join Two Tables

April 04, 2022 - by

Use VLOOKUP to Join Two Tables

Problem: My I.T. department gave me a data set with Item Number, Date, and Quantity sold. They didn’t put an item description in there. If I call back and ask them to re-do the file, it will take 3 weeks. Can I quickly fill in the item descriptions?

A data set with Item # in A, Date in B, Quantity in C. You need to fill in the description in D.
Figure 410. You need item description here.

I have a second table that maps item number to item description.

Strategy: Use VLOOKUP. This is the single most important function in Excel. VLOOKUP will save you time and time again.

A lookup table provides SKU in the first column and Desciption in the second column
Figure 411. You have a table with the item descriptions..

In this situation, VLOOKUP requires four arguments:

  • The value to look up. In this case, it is cell A2.
  • A table with the lookup value in column 1 of the table. You always want to enter the table with dollar signs throughout the reference. That way, you can copy the VLOOKUP formula to other rows. In this case, the table is in $L$3:$M$30.
  • A column number in the table to return. Unlike Lotus 1-2-3, Excel will allow you to return the key column in the table, so the SKU column above would be column #1. The Description column is column 2.
  • The fourth argument in VLOOKUP is always FALSE. If you don’t put a FALSE as the fourth argument, Excel will give you results that almost match. This is NEVER what you want when you are solving these types of problems.

The formula for D2 is =VLOOKUP(A2,$L$3:$M$30,2,FALSE).

Additional Details: You can use a wildcard in VLOOKUP. To find the first entry that starts with A, use =VLOOKUP(“A*”,Table,2,False).

VLOOKUP the part number in A2 looking at the table in L3:M30, returning column 2, with False as the fourth argument. The False says you only want an exact match.
Figure 412. If you can enter the VLOOKUP with your eyes closed, I will give you a spot on my team when Excel becomes a sport in the Olympics.

Additional Details: I used to be a manager of financial analysis. On the job posting form when I was hiring financial analysts, I would list a single requirement: “Can do VLOOKUPs in your sleep”. It really is the single most important Excel skill.

Additional Details: To me, the world breaks down into two kinds of people, those who can do VLOOKUPs, and everyone else.

This article is an excerpt from Power Excel With MrExcel

Title photo by Matthew Waring on Unsplash