VLOOKUP is a powerful and flexible tool that can be used for a variety of applications. Have a table of data and want to find information from it with just a few inputs? VLOOKUP is the answer.
It’s used in business extensively and has the flexibility to be a very powerful tool, especially when used in conjunction with other excel functions.
Thankfully, it’s simple enough that the layperson can learn it quickly and use it in their day-to-day lives.
In this article, we’re going to provide a VLOOKUP tutorial and step-by-step, as well as a few practical examples of how you can make the most of it in your work and personal life.
Table of Contents
What is VLOOKUP?
At its most basic, Excel’s VLOOKUP function is a tool to source a single item from a block of information in the form of a table (sometimes referred to in Excel-speak as a “range”).
VLOOKUP is short-form for “Vertical Lookup”, which means that the function searches through a vertical list of information - this means that each entry in your table needs to be in a row-wise format, similar to reading a pizza menu. Please note that this is not to be confused with HLOOKUP, which requires each entry in your table to be presented in columns, rather than rows.
For example, a pizza menu presents its items in each row with a description of the item in the leftmost column and then the pricing for various sizes to the right of it. Each row in the table represents a different pizza preparation.
Let's assume you're interested in a 14” La Barra Supreme. You’d first find out which row corresponds with the La Barra Supreme, then you’d find out which column corresponds with the 14” pizza, and where they intersect is the price.
This is exactly how VLOOKUP does it.
If you were to do a VLOOKUP for the 14” La Barra Supreme the function would return 30.
What other things can we do with VLOOKUP?
Beyond the basic functionality of VLOOKUP, when it is combined with other concepts, it truly becomes a very powerful tool.
Here are just a few things you can do with it:
Attach metadata to entries for grouping / aggregation
Combining multiple data sets into one table
Reconciliation / comparing items across two tables / cross-referencing
Dynamically updating graphs update using a drop-down
How does VLOOKUP work?
- lookup_value
- table_array
- col_index_num
- range_lookup (optional)
lookup_value
The lookup_value input is the search term which will be compared against the leftmost column of the source data that is referenced.
=VLOOKUP(“La Barra Supreme”,...
If you prefer to link your formula to other cells that contain the word “La Barra Supreme” directly, then no quotes are necessary
=VLOOKUP(B7,…
table_array
The table_array input is the data source through which you will be searching. The leftmost column of table_array will be searched using the lookup_value to select the correct row.
Pro Note
The VLOOKUP search is performed from top to bottom, so if there are repeated values in the table, only the first instance will be chosen and all subsequent instances will be ignored.
Ensure that your table_array is wide enough to include the return column that you’d like to pull data from and that it is tall enough to encompass all entries in your data set - omitting these will turn into error troubleshooting (more on this later).
col_index_num
The col_index_number field tells VLOOKUP which column in the range you’d like to pull the data from once the correct row has been identified.
Once the correct row and column have been identified, the output will be sourced at the point where they intersect.
range_lookup
The range_lookup is an optional field that allows you to determine how precise the lookup is allowed to be.
False (or "0")
Requires an exact match to return a result
True (or any non-zero number)
Allows for approximate matches and returns the nearest result)
CAUTION: The leftmost column in your table must be in ascending order for VLOOKUP to provide an accurate result.
If the range_lookup field is left blank, then the VLOOKUP is set to TRUE by default.
VLOOKUP Step-By-Step
Before we begin - do you like to be hands-on?
Download the template to follow along with the tutorial
Step 1 (optional): Arrange and sanitize the input data
As with any data analysis exercise, the old adage “garbage in, garbage out” rings true. Thus, it is imperative to both structure as well as sanitize your data to the best of your ability prior to analyzing it so that you can avoid frustration down the line.
Structure your data
Remember that VLOOKUP depends on the leftmost column of the table_array input and that any information beyond the left side of table_array will be inaccessible. As such, you must always ensure to put the column that you plan to search through as the leftmost column of your table.
Taking this step will allow you to search through all of your data without having to rewrite all of your VLOOKUP formulas if you have to re-order your columns later in the process.
Sanitize your data
If your search column comes from a data source that is written manually, rather than programmatically, there is a very high likelihood that it will be difficult to search through due to inconsistent spacing, spelling, or other errors.
Go through your data to =TRIM, check for spelling errors, or find other issues that can make your life difficult when applying the VLOOKUP function.
Below is an example table that we’ll be following for this exercise. In this example, we’re playing the role of a used car dealership employee looking through their inventory of vehicles:s
Step 2: Insert your formula
Keeping your spreadsheet organized is of paramount importance, so where you put your formula will drive its usability and ease of troubleshooting.
If you are doing a one-off search of a data table, it is easiest to make some room above the data table to place your VLOOKUP - insert 2-3 rows above your table header by highlighting your header row and pressing CTRL + SHIFT + Plus Sign (+) (Command instead of SHIFT on Mac).
Label your VLOOKUP formula with a descriptive title in the cell to the left of where you are placing your formula.
Then apply Freeze Panes so that your header row, plus the full area above your data table will always remain visible. To do this, highlight the row beneath your header row (the 1st entry of your data), then in your menu, go to View-->Freeze Panes-->Freeze Panes. This will lock the area above where you have highlighted.
Doing this will allow you to view as many of the columns of your table as possible when you are modifying your VLOOKUP formula.
Step 3: Assign your search term
Manually assign your search term (lookup_value) directly into the VLOOKUP formula or make a cell reference to another cell containing your search term.
Any text should be wrapped in quotes, while numbers can be inserted directly into the formula.
Once the lookup_value has been set, insert a comma into the formula and move on to the next step.
Step 4: Assign your data source
Now that you have identified your search term, find the column that corresponds to where you want to search for that term (in this case, Column A), and choose the top- and leftmost entry in the table - ensure not to select the header.
Drag the bottom-right handle of the selection so that your table_array covers all of the columns that you’re interested in pulling from and includes all of the rows in your data set.
Once the table_array has been set, insert a comma and move on to the next step.
Step 5: Pick your return column from your data source
In this VLOOKUP example, we’re trying to find out which vehicle model corresponds to a particular VIN number.
Always remember that our column numbers are relative to our table_array, so as we’ve defined it, column 1 is VIN, column 2 is Year, column 3 is Make, and so on. Since we’re interested in sourcing the model of the car, we’ll need to pull column 4.
This makes our formula so far:
To elaborate how column numbering works: If instead we had swapped the VIN column into column B (and Year moves to column A) and we performed the same search, our table_array would go from columns B through F. This change would make it so our column 1 would be VIN, column 2 would be Make, and column 3 would be Model.
Once you have assigned col_index_num, move on to the next step.
Step 6: Assign exact match or approximate match
Since this lookup is being done precisely on a VIN, an “exact” match is appropriate, so we should set range_lookup to be FALSE or 0, then press Enter. In excel, FALSE and the number 0 are logically equivalent.
Pro Note
Anything that is not FALSE or any non-zero number is equivalent to logical TRUE. For example, 0.0001 would be logically equivalent to TRUE because it is not equal to zero.
If, on the other hand, we wanted to perform an approximate match VLOOKUP, such as calculating an incremental tax bracket type of calculation or finding the closest alphabetical match in a word list, we should set range_lookup to TRUE or 1.
Now you’re done! The VLOOKUP formula we set up should return the corresponding model to the VIN “EFG567”, which is “Soul”.
Troubleshooting
If you’re getting error messages, use the following checklist to troubleshoot:
- Confirm there are no typos in your lookup value
- If searching through numbers, ensure they are not formatted as text (search for a leading apostrophe before the number)
- Confirm that the lookup value is less than or equal to 255 characters in length
- Ensure col_index_num is an integer that is 1 or greater and falls within the bounds of the table_array
Tips and Tricks
Absolute references vs. relative references
An absolute reference in excel is a way of locking or fixing a part of a formula in place so that when you drag or copy a formula in any direction, it will stay in place, rather than moving with the destination cell.
For example, if you have an exchange rate that you want to apply for your whole spreadsheet, you’ll set a dedicated cell, let’s assume the value is in cell B1. You can call that exchange rate by typing the formula “=B1” and you’ll get the exchange rate in cell B1.
Formula
Value
We will often want to re-use that same cell reference across multiple calculations, but the “=B1” syntax is what is called a “relative reference”, which means that excel does not treat “B1” as “B1” - it thinks of the formula as “Y rows away + X columns away from my current position". If you were to copy that formula over to the right one cell, the formula would change to “C1”, because it would say “I want the cell that is Y rows and X columns away from me."
To fix this, anytime we want to copy formulas, we must consider which rows and/or columns must be fixed (treated as an “absolute reference”).
- B1 - Relative reference
- $B1 - Absolute column, but row is relative
- B$1 - Absolute row, but column is relative
- $B$1 - Fixed reference
To elaborate the difference among the various locking methodologies, see below:
In the above example, the column is locked, so as you copy/paste to the right and down, the reference cell trails downward but not to the right - it’s as if the selection is riding along vertical rails down column B.
In this one, the row is locked, so as you copy/paste to the right and down, the reference cell trails to the right but not downward - it’s as if the selection is riding along horizontal rails across row 13.
In this final example, both the row and column are locked (a fixed reference), so as you copy/paste to the right and down, it always references the original cell, displaying “Corner” in the full table.
As you go to try this out don't be discouraged, as getting the hang of absolute vs. relative references will take some trial and error - it will become more intuitive as you practice.
Case sensitivity
It is key to note that the VLOOKUP search is not case sensitive, so the following phrases are equivalent to one another:
- “La Barra Supreme”
- “la barra supreme”
- “LA BARRA SUPREME”
- … and any permutation therein
Leading and trailing spaces
Whenever using VLOOKUP, you must be very careful with leading or trailing spaces, because they will be interpreted as different from teone another. The following values are not equivalent to one another:
- “La Barra Supreme”
- “ La Barra Supreme” ← Leading space
- “La Barra Supreme “ ← Trailing space
PRO TIP: to normalize your data before searching through it, run the TRIM function on the leftmost column to clear any leading or trailing spaces in text items. This will not eliminate any spaces that are not leading or trailing.
Dynamically choosing the return column
If you think you might re-order your columns at some point, you might want to consider using the MATCH function on your header row. The MATCH function will search for the name of the header column and return a number for where to find that header name - this output can be plumbed into the col_index_num of your VLOOKUP.
Alternatives to VLOOKUP
VLOOKUP is great but has its limitations. For example, you can’t select items that fall to the left of your search column because it falls outside of your table_array.
Learn about techniques like INDEX MATCH or OFFSET MATCH, both of which replicate the functionality of VLOOKUP, but with more flexibility and allow you to get around VLOOKUP’s limitations. These are very powerful advanced techniques, but they are difficult to audit, so use with caution.