The VLOOKUP function is one of the most useful tools in Excel's arsenal. Experienced data workers regularly use the VLOOKUP function to clean and consolidate data.
At its heart, VLOOKUP is a search function. It can sift through a huge table and tell you an answer to a specific question. And since it's a function, it can keep answering this question dynamically, as you input new data into your table. The trick is learning how to ask the question.
Before we start, let’s define arguments. Arguments are required inputs for a function to return a desired value. VLOOKUP has four arguments:
- lookup_value: the focus of your search. It could be a place, a person's name, or a product category. VLOOKUP will retrieve information from the lookup_value's row.
- table_array: this defines which part of your spreadsheet you want the function to search.
- col_index_num: this tells Excel what column to search for values that match your lookup_value's row.
- range_lookup gives Excel instructions on what to return if it can't find anything. (This argument is optional.)
We'll go through these four arguments one by one in this simplified example. Imagine you're a health officer with a spreadsheet full of all the known cases of various diseases throughout all the counties in your state. You want to find the count of a specific disease (West Nile) in a specific county (County 3).
Step 1: Select your lookup_value
Once you select a blank cell to put your VLOOKUP function in, your first task is selecting your lookup_value. In the chart below, this argument is a kind of disease. We'll pick West Nile—found in cell A6.
Note that the lookup_value must be in the leftmost column of whatever part of the spreadsheet you want VLOOKUP to search. We'll cover how to specify this region in the next step.
Step 2: Define your table_array
Our second argument is the table_array or search area of the function. The table_array must contain both data and the labels. In this example, the labels are diseases, and the data are the counts of those diseases.
We specify the array A4:E7. In the next step, we'll tell our function which column in this array to search.
Step 3: Input the col_index_num
The third argument is the col_index_num, which is the column in the array with the data we wish to collect. In the example, we wish to collect cases from County 3, which is the fourth column of our array, so we enter 4.
Note that the number for the col_index_num is relative, not absolute. County 3 is the fourth column from the left on our selected array, which is why we enter 4. Entering "D" for County 3 won't work.
Step 4: Use the optional range_lookup.
The last argument, range_lookup, tells what the function should return if there is no match for the lookup_value. If we enter FALSE and finish our function, we get a answer of 4 West Nile cases in County 3. If we enter FALSE with no exact match, we’ll get an error. (This error essentially communicates that the search turned up nothing.)
FALSE is the most common entry range_lookup. If you enter TRUE and the function cannot find an exact match, it will return an approximate match—that is, the cell value immediately below.
The above example is extremely simplified, so the function may not seem much easier than simply looking at row 6 and matching it with column D. But for spreadsheets with hundreds or thousands of rows, VLOOKUP is a huge time-saver.