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?
- 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.
- 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).
- 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.