The VLOOKUP function searches for a specific value in a dataset.
If it finds it, it returns another value from a different column in the same row.
This allows you to look for data in one spreadsheet and bring it to another spreadsheet in a few seconds
And that’s what makes VLOOKUP one of the most time-saving functions in Excel.
Let me show you how to use VLOOKUP in 4 simple steps.
VLOOKUP function example
In the example in this VLOOKUP tutorial, you have a list of sales made by different sales representatives (to the left).
To the right, you have a small list of order IDs your boss sent you – but without any prices.
She wants you to find the price of those specific orders.
And she needs them in 5 minutes. You better hurry…
VLOOKUP to the rescue
You need to look for the specific order IDs in column A and return the corresponding price from column C.
So, how to use VLOOKUP? I’ll show you, step-by-step.
Step 1: The lookup value
Select the cell where you want the result to be and start the VLOOKUP function by typing:
=VLOOKUP(
Now, a tooltip appears that shows you the VLOOKUP syntax.
The syntax is the input an Excel function needs to return the output you need. Each input is called an ‘argument’.
In this guide, we dive into each argument individually.
The first argument is the lookup_value.
The lookup value is what you’re looking for.
Click or type a reference to the cell with the value you’re looking for.
You’re looking for the specific order ID. Click the order ID (from the list your boss sent you) to make a reference to it.
Then write a comma to tell VLOOKUP you’re ready to input the second argument in the syntax.
Your VLOOKUP function should look like this by now (replace the cell reference with your own):
=VLOOKUP(E3,
Easy so far?
Step 2: The table array
Now Excel asks you to write the second argument into the VLOOKUP function: the table_array.
This is where you are searching for the lookup value (from step 1, remember?).
This is a bit trickier but don’t worry, it’s over in a minute
The table array is a range (group of cells).
This range must contain both the column you’re looking in and the column you want to return something from.
Select or type the range – like you would any other group of cells.
The table array should contain columns A (the lookup column), B, and C.
That means VLOOKUP searches for the order ID in column A and returns the price from either column B or C (you decide which in the next step).
Place a comma to wrap up step 2.
Your VLOOKUP function should look like this by now:
=VLOOKUP(E3, A:C,
VLOOKUP looks from left to right!
VLOOKUP looks for the lookup value in the leftmost column of the table array. That means whatever you want to return must be located somewhere to the right of the column you’re searching in.
If it’s not – you either need to rearrange the columns or use INDEX MATCH instead.
The hard part is over.
Now you need to decide what column you want to return something from.
Step 3: Column index number
The third argument of the VLOOKUP syntax is the column_index_no.
The column index number tells VLOOKUP which of the columns in the table array (from step 2) you want to return a value from.
If we zoom out, this is the reason you’re using VLOOKUP in the first place! So, tell me, why is that?
“Because I want to return the price of the order ID I’m looking for.”
Good! Focus on the first part of that sentence.
“Because”
No, not that…
“I want to return the price”
BINGO!
Write the column number you want to return something from.
The price is in column C. And column C is the 3rd column in the table_array. So, write 3.
And write a comma to indicate you’re ready for the fourth and last argument in the VLOOKUP function syntax.
Your VLOOKUP function should look like this by now:
=VLOOKUP(E3,A:C,3,
The column index number follows the table array. If the table array was columns B, C, and D, the first column (column index number = 1) would B, and the third column (column index number = 3) would be D.
We’re almost there…
Step 4: Exact match or approximate match
The fourth argument of the VLOOKUP function is the range_lookup which decides the lookup “mode”.
Most of the time you’ll need to use “exact match mode”. Unfortunately, this is not the default, so you need to let Excel know this with the range lookup argument.
The exact match means the VLOOKUP function will look for exact values exclusively. Only values that are 100% identical to the lookup value (from step 1) are considered an exact match.
Write the word: FALSE to use exact match.
Now wrap up the formula with an end parenthesis, and you’re good to go
Your formula should look like this by now:
=VLOOKUP(E3,A:C,3,FALSE)
Press enter!
Approximate match
Now, that doesn’t mean the other mode, “approximate match mode”, is not useful. It’s very convenient for looking up lookup values within an interval.
To use “approximate match mode”:
- Write TRUE in the range lookup argument
- Sort the leftmost column of your table array from lowest to highest
Because it’s so rarely used, I won’t dive deeper into approximate match in this VLOOKUP tutorial.
That’s it – Now what?
Well done on learning how to use VLOOKUP – the most popular advanced Excel function of all time.
Now you can search for a value and return another value in the same row from another column.
It wasn’t so scary after all, right?
VLOOKUP is the most popular non-beginner Excel function of all time and is used in a very wide range of intermediate and advanced formulas.
As soon as you start advancing your Excel skill level, you recognize that data in your organization is spread into many different Excel files.
The VLOOKUP function’s ability to join the data you need from these Excel files is extremely handy
But 2 other functions are just as useful: IF and SUMIF.
Click here to join my free 30-minute video course and learn IF, SUMIF, data cleaning, and more.
Other relevant resources
If you thought this was a bit easy then you should take it to the next level with the kings of lookups: INDEX MATCH or XLOOKUP.
The V in VLOOKUP stands for vertical. That means VLOOKUP can only perform a vertical lookup, where data is listed vertically. If your data is listed horizontally, you should check out HLOOKUP.
Earlier I talked a bit about making references to ranges. I don’t have to lock my A:C reference, but you might have to with your cell references. Click here to learn all about references.