This tutorial will explain the VLOOKUP function and walk through two examples demonstrating how to use the VLOOKUP function.
What is VLOOKUP?
The VLOOKUP function is one of the most popular functions to learn in Excel. Knowing how to do this is what separates the novices from the pros. The VLOOKUP function is used to find information in one column and return information from the same row of another column. Said more simply, the VLOOKUP allows you to find a specific value in a table based on a lookup value like an ID or name.
Example 1: Zach's Address
Say for example you had a table with all of your friends, their addresses, and their phone numbers (in a world before cell phones). Someone asks for your friend Zach's address, but since we have a lot of friends, it may take some time to scroll through the whole list to find Zach's address. If we know how to use the VLOOKUP function well, then we are in good shape. All we'd need to do is tell Excel that we are looking for Zach's address by typing in the right inputs.
There are 4 inputs to the VLOOKUP function - the lookup value, table array, column number, and range lookup. In this case, our lookup value is Zach because that is who we are looking up. Our table array is our table with all of our friends' addresses and phone numbers. Since the addresses are in the second column, our column number will be "2." Lastly, the range lookup will be FALSE or 0 which means we want an exact match (don't worry about this one, for now, just use FALSE as your default).
Example 2: ID B75
Now, for a bit more of a practical example. Say we have a list of employees with ID’s, levels, and departments. Each employee ID corresponds to a person who will have a job level and department associated with them. As we are looking through our sales reports, we notice that employee B75 is doing a tremendous job and we want to congratulate them. So, we’ll go over to our Excel sheet with our list and use the handy VLOOKUP function. We’ll type in our inputs to find B75 and we’ll get the answer we were looking for. Congratulations, Sally!
Become a master of Excel to learn more handy tools like this in our Excel classes: