fbpx

VLOOKUP – Everything you need to know (and then some)

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.

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.

Pizza Menu

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.

Image illustrating how VLOOKUP searches through a table - first down the leftmost column then to the right based on the inputs.

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:

Grouping

Attach metadata to entries for grouping / aggregation

Combining data sets into one table

Combining multiple data sets into one table

Cross-Referencing

Reconciliation / comparing items across two tables / cross-referencing

Dynamic Graphs

Dynamically updating graphs update using a drop-down

How does VLOOKUP work?

Image showing the VLOOKUP formula inputs and what each of them mean.
The VLOOKUP function accepts three to four inputs:
  • 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. 

In our earlier example, the string “La Barra Supreme” in quotes would be used if directly inserted into the equation:
=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. 

VLOOKUP illustration of the leftmoset column in table_array

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.

Image showing a visual representation of how VLOOKUP functions

range_lookup

The range_lookup is an optional field that allows you to determine how precise the lookup is allowed to be. 

There are two possible types of values range_lookup will accept:

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

Image showing the data table being used for this VLOOKUP example

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. 

Image showing the steps to activate Freeze Panes in Excel

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.

Example VLOOKUP formula with lookup_value filled in

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.

Example VLOOKUP formula with lookup_value and table_array not completely filled in

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. 

Example VLOOKUP formula with lookup_value and table_array completely filled in

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:

Example VLOOKUP formula with lookup_value, table_array, and col_index_num completely filled in

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. 

Example VLOOKUP formula showing the options for the range_lookup field

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”). 

The syntax is as follows, following our example above:
  • 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:

Excel Absolute References - Locked Column

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.

Excel Absolute References - Locked Row

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.

Excel Absolute References - Both Locked

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.