The VLOOKUP function searches for a specific value in a dataset.
If it finds it, it returns the corresponding 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.
Download and open the free workbook, and let me show you how to use the Excel VLOOKUP function in 4 simple steps.
VLOOKUP 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, which is the ‘return value,’ 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 formula 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, where each input is also known as a parameter or ‘argument’.
Kasper Langmann, Microsoft Office Specialist
Let’s look at 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 secondargument into the VLOOKUP function: thetable_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, or table range, is a specific 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 span the number of columns needed, including columns A (the lookup column), B, and C.
That means VLOOKUP searches for the order ID in column A and returns the price from the second column, B, or C, depending on your selection 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 lookup table or table array, aiming to find the matching value.
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 oruse the INDEX MATCH function instead.
Kasper Langmann, Microsoft Office Specialist
The 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 col_index_num (column index number).
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. Column C is the 3rd column in the table_array. So, write 3.
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 be 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, but it’s important to note that VLOOKUP is not case-sensitive.
Only values that are 100% identical to the lookup value, aside from case differences, 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 Microsoft 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 across many different Excel worksheets and 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, formatting techniques, and more.
Other relevant resources
There’s much more to VLOOKUP! Check out these tutorials:
- VLOOKUP with Wildcards (asterisk) for partial match searches
- VLOOKUP not working? Fix the ‘not found’ #N/A error (and much more)
- Using VLOOKUP in VBA (advanced)
- Enhance your error handling with the IFERROR function in our dedicated tutorial.
Looking to move on from VLOOKUP? As a foundational lookup function, it sets the stage for advancing to more complex tools like INDEX MATCH or the XLOOKUP function.
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👋
Author
Written by Kasper Langmann, co-founder of Spreadsheeto and a certified Microsoft Office Specialist.
This tutorial reflects over 60 hours of dedicated research, testing, and writing.
Last updated on February 1st, 2024.
One last thing before you go: make sure to sign up for my free Excel course!
