Now it’s time to get into the syntax of VLOOKUP.
“Syntax” is just a big ugly word that doesn’t make much sense unless you’ve heard it before.
But what it means is really simple.
“Syntax is a combination of the things you need to put into a function to make it work.”
A VLOOKUP function needs these 4 inputs to work:
- Lookup_value = What you are looking for
- Table_array = Where you are looking
- Col_index_num = What we want to know
- [range_lookup] = Whether we want to be precise or approximate in our search
When we start entering the formula in a moment, you’ll see all of these phrases inside a tooltip box below the cell you’re typing in.
All these inputs to the VLOOKUP formula must be separated with a comma (like in most other functions).
Every time you put a comma, the tooltip box tells you how far you are in the formula, by bolding the current part of the syntax.
But enough with all the technicalities!
Imagine the VLOOKUP function doing the same as you when you want to look something up.
I know phonebooks are pretty outdated by now, but let’s say that we’re using one right now.
- You’re looking for Nate Harris’ salary. A proper phonebook may not contain that kind of information but for the sake of this example – let’s say it does.
- You acknowledge that we’re looking for the name ‘Nate Harris’ in the phonebook. This is your identifier. The piece of information you’re searching for with your eyes. In Excel terms, this is your lookup value.
- You browse through the pages and find Nate Harris. You place the tip of your finger right below his name and move your hand to the right past information like his address, phone number, and other boring stuff. And there you have it! His salary! 3 inches to the right of his name. Then you write it down or do whatever you want to do with it.
That’s exactly how a VLOOKUP works!
So our lookup value is typed into cell F2 and then used in our function.
I guess you’re getting the point by now? So let’s start building the formula!
Type this into Excel in cell F3 (also shown in the screenshot right below):
And what comes next is the name of the employee we want to find more information about.
As a lookup value, you can either type in the name (“Nate Harris”) or put in a reference to the cell where you’ll type in the name.
The last option is usually the best and our sheet is set up to have the name entered in cell F2.
So we’re going to select cell F2 (or type F2 in the formula bar) and type a comma to move on in the formula.
In this case, the VLOOKUP formula will not be copied, but the sheet structure might be changed at some point.
To prevent the formula from being messed up, we lock the reference to cell F2.
This is achieved by putting the marker inside (or around) the F2 and pressing the shortcut F4 (on MAC: Command + T). Like done below:
Your formula now looks like this: