About Consultant Courses FREE EXCEL TRAINING
How to use VLOOKUP in Excel
for Dummies (2022 Tutorial)
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’.
Kasper Langmann, Microsoft Office Specialist
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,
Pretty easy so far, right?
Let’s move on.
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.
Kasper Langmann, Microsoft Office Specialist
Hard part’s 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.
Kasper Langmann, Microsoft Office Specialist
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.
Take care
"By far this is the best training on
the internet."
I've just released a free Excel course that I think fits
you perfectly.
It's 3 lessons, got free exercise-files... and you
learn about IF, SUMIF, and VLOOKUP (+more).
+50.000 have already tried my training. Now it's
your turn!
NEXT
CONTACT CUSTOMER SERVICE COURSES OUR BEST FREE
INFORMATION CONTENT
– Customer – Zero to Hero:
Spreadsheeto ApS support Become an Excel- We’ve published
Superuser in 14 +100 Excel-
Address: – Terms and hours tutorials on our
Sømosevej 17, 2740 Conditions blog. Here are our
Skovlunde, – VBA top 3 picks:
Denmark. – Login Masterclass:
Become a VBA-Pro 1: The last guide to
VAT-ID: DK- in 20 hours VLOOKUP you’ll
40428631 ever need
– Power BI
Email: Essentials: Learn 2: How to Delete
hello@spreadsheeto.com Power BI in 12 Blank Rows Easily
hours
– About
3: INDEX+MATCH
– Contact
– Team Solution: with multiple
– Consultant For Businesses and criteria (3 easy
Organizations steps)
Pssst… Make sure
to check out our
free Excel training
that adapts to your
skill level too!
Spreadsheeto. We love Excel. You will too.
