Don’t worry, this process isn’t nearly as sophisticated as it sounds.
Now, let’s take a closer look at our sample file.
Let’s start with step 1: placing your VLOOKUP…
Step 1: Placing the VLOOKUP formula
Start by selecting the cells where you want the result of your calculation (the VLOOKUP function) to go.
We’re going to be looking for something, so include a spare cell to use for “searching”.
In this case 4 cells in total.
Two for the name and two for the salary.
Select cells E2:F3. Go to the “Font” group of the “Home” tab on the ribbon and click the little arrow next to the “Borders” button.
Right-click and select “Format Cells”. Then go to the “Border” tab.
Select a thick border from the “Style” options and click on the “Outline” preset. Then click the horizontal line in the mid of the example.
Now select a thinner border style and click the vertical line in the mid of the example.
Type in “Name” in cell E2 and “Salary” in E3 and make the text bold.
Now we have a pretty little area to fill in what we’re looking for (cell F2) and our formula with VLOOKUP below (cell F3).
That was pretty simple, right?
Move on to step 2…
Step 2: What are you looking for?
Now it’s time to get into the syntax of VLOOKUP.
Here’s what “syntax” means in the case of an Excel function:
“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
All these inputs to the VLOOKUP formula must be separated with a comma.
When you write a formula, you’ll see all of these phrases inside a tooltip box below the cell you’re typing in.
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.
Here’s how to make sense of VLOOKUP’s syntax!
Imagine the VLOOKUP function doing the same as you when you want to look something up.
Say we have a book with people’s names, addresses, phone numbers, and salaries.
You’re looking for Nate Harris’ salary.
You acknowledge that you’re looking for the name ‘Nate Harris’ in the book. 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 function 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):
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.