The VLOOKUP function in Excel is one of the most useful features the software provides. It makes searching for and automating the input of data easy and efficient, a must-know skill for anyone working with large databases and spreadsheets.
Aside from being a convenient function, it can also be quite challenging for beginners just starting to learn how to use it. In this guide, we’ll use an Excel VLOOKUP example scenario to help you better understand the function’s usage and application.
Take note, this guide assumes a general understanding of Excel’s basic functions and terminology. If you’re new to the software, consider taking a beginner’s level course and learn the foundations of Excel before continuing on. There are plenty of free tutorials available as well.
What is VLOOKUP?
The V in VLOOKUP stands for vertical, and the LOOKUP portion represents exactly that – the feature’s ability to look up and extract data, vertically, from another table, based on an associated value.
Excel’s official definition of VLOOKUP in its function database is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.
It sounds a little more complicated than it actually is, so let’s take a look at an example instead.
Excel VLOOKUP Example
Say you have a list of products located in a table in Worksheet 1. In the first column of your table is the item ID for each product, in the second column is the product category, and in the third column is the item name. This worksheet acts as a reference, or a key, for your inventory of products and their associated descriptors.
In Worksheet 2, you have a list of orders to fulfill, but only the item ID is listed next to each customer’s name. You want to create a table to track which customers bought which items, using the item’s name instead, but to do so manually would be a long, pain-staking task.
That’s what VLOOKUP is for! It can retrieve the item ID from your reference worksheet, and input it into your new table, using a simple formula.
Let’s see it in action.
Step 1 – Insert a function
The first thing you want to do is highlight the cell you want your retrieved information to be input into – in our example, that would be cell C2 in our Orders worksheet – and then insert a function.
To insert a function, navigate to the Formulas tab at the top of the screen, and click the first button at the far left that says Insert Function. You can also press Shift+F3.
Once you click Insert Function, the following box should appear:
Type VLOOKUP into the search bar, hit GO, make sure the VLOOKUP function is highlighted in the search results, and click OK.
Step 2 – Input function arguments
This is where it might get confusing for beginners. What are the function arguments of VLOOKUP? What do they mean, and how do I know what to input to get the results I want?
Let’s take a look at each specific argument, and try to break things down.
- Lookup_value: In which cell is the identifier this function should be retrieving associated information about?
- Table_array: From where must this data be retrieved?
- Col_index_num: Which piece of information should be retrieved?
- Range_lookup: An optional parameter we’ll explain later. Learn more about it here.
For our lookup_value, we need to select the cell with the identifier we want to use to pull specific information from our reference worksheet. In our example, this means we want to select the item ID number in our Orders worksheet for which we want to find the item name.
Item ID RA22981 in cell A2 is first in our list, so either highlight A2 to input it automatically in the text field, or type in A2.
For the table_array value, we need to specify the table of data that our unique identifier and its descriptors are located. In this case, navigate to our Product Keys worksheet, or reference worksheet (you can do this without exiting the VLOOKUP prompt), and highlight all the cells within the table except for the headers.
Your table_array parameters should look like: ‘Product Keys’!A2:C15
This means the VLOOKUP function will search within the range of cells A2 and C15 in our worksheet titled Product Keys, for the information associated with A2, our lookup_value that we specified previously.
Next, we use col_index_num to specify from which column in our range of data we should pull our descriptor from. In our case, we want the names of the products associated with its item ID. Because our Product Name list is in Column C, or the third column, we should input the number 3.
The range_lookup is an optional parameter that depends entirely on the way data in your reference worksheet is sorted. If the reference data – in this case the item ID numbers in our Product Keys worksheet – is sorted alphabetically or numerically, in ascending order, we can enter TRUE or leave the parameter blank.
If the data is not sorted in alphabetical or numerical order, or if it’s sorted in descending order, we have to insert a value of FALSE. Our item ID numbers are not sorted in any specific order, so we will enter FALSE.
If you want to know more, consider finding an Excel course from this giant bundle of courses that works for you, and become a pro!
When you’re done inputting all the necessary parameters, your VLOOKUP Function Arguments prompt should look like this, and your VLOOKUP function should read:
This means you’ve told VLOOKUP to take the item ID in cell A2 of our Orders worksheet, search the Product Keys reference worksheet through cells A2 to C15 for all the item IDs and associated descriptors, and pull the data related to our specified item ID from the third column.
When you’re done, hit OK!
Step 3 – Repeat!
And so, based on VLOOKUP’s computations, it turns out our first customer Adam ordered the “eSports Live 2011” DVD. If we want to know what the rest of our customers ordered, just continue following the same steps, changing only the cell name in the lookup_value field.
… and so on!
Congratulations! You now know how to use the Excel VLOOKUP function. For more Excel tutorials, consider the following:
- Mastering MS Excel® (Beginners/Intermediate)
- Excel Pivot Tables Demystified for Beginners
- Microsoft Excel – Advanced Formulas And Functions