How to use the VLOOKUP Function in Excel
VLOOKUP is one of the most powerful and useful functions in Excel and, if you understand how it works, it’s easy to use… but don’t tell anyone. Let your coworkers think you’re an Excel genius!
How VLOOKUP Works
Whether you realize it or not, you probably already know how VLOOKUP works. Take a look at the airport departures sign (above). If I told you that you were on flight EF 0506 to Toronto, would you be able to tell me what time your flight leaves? Of course you would!
VLOOKUP is based on a sequence of steps that most people use on a regular basis. Here’s what happens when you look for your departure time:
- You look in the first column for flight EF 0506.
- You locate Flight EF 0506.
- You scan across to find the departure time.
Your flight leaves at 5:20… better make your way to the gate!
Using VLOOKUP in Excel
VLOOKUP follows the departures board example closely. It scans down the first column of data in a range and, when it finds a match, it returns a related value from another column in that row.
VLOOKUP requires 4 arguments. Here is the syntax (or format) of the parameters needed when you use it:
I’ll explain each parameter as we enter a VLOOKUP function in Excel.
lookup_value – Describes what you want to look up.
Think about the airport departures board scenario. I want to find out when my flight to Toronto leaves. First, I need to look up flight EF 0506. I could type “EF 0506” into the VLOOKUP function but I’m better off using a cell reference.
I’m going to click in cell B19 and type =vlookup(B18.
Don’t forget the equals sign!
table_array – Describes where your source data is located.
In the departures table, our source data is contained in the range A3:E14. Remember, VLOOKUP will always scan the leftmost column of the selected range for a match so select your table array carefully. Also, if you are going to copy the function to other cells so you can perform several lookups, you need to refer to the table array using absolute addresses ($A$3:$E$14).
I’ll type a comma (,) then drag to select cells A3:E14.
col_index_num – Indicates which column you’re interested in.
In the departures table, ‘time’ is in column 3.
I’ll type another comma (,) followed by the number 3.
range_lookup – Indicates that you’re looking for the closest match.
The closest match is appropriate in certain circumstances when the first column contains numbers. In this example, we want to find an exact match for flight EF 0506.
I’ll type another comma (,) followed by the word false and a closing bracket then I’ll press ENTER.
In our example, the complete VLOOKUP function would be:
I hope you’ve enjoyed my explanation of Excel’s VLOOKUP function. It’s one of the most popular topics in our Excel Data Management course. If you like this article, please share it with your friends via social media.
If you have any questions about VLOOKUP, or there is another topic you’d like to read about, let me know if the comments below!