VLOOKUP Guide

Lookup and retrieve information in a table

What is VLOOKUP in Excel?

The VLOOKUP function in Excel is a tool for looking up a piece of information in a table or data set, and extracting some corresponding data/information.  In simple terms, the VLOOKUP function says the following to Excel, “look for this piece of information (i.e. “bananas”) in this dataset (a table), and tell me some corresponding information about it (i.e. the price of bananas).

Learn how to do this step by step in our Free Excel Crash Course!

 

VLOOKUP Formula

=VLOOKUP(lookup value, table range, column number)

To translate this to English the formula is saying, “look for this piece of information, in the following area, and give me some corresponding data in another column”.

 

How to use VLOOKUP in Excel

 

Step 1: Organize the data

The first step to effectively using the VLOOKUP function is to make sure your data is well organized and suitable for using the function.

VLOOKUP works in a left to right order, so you need to ensure that the information you want to look up is to the left of the corresponding data you want to extract.

For example:

 

vlookup step 1

 

In the above VLOOKUP example you will see that the “good table” can easily run the function to lookup “Bananas” and return their price, since Bananas are located in the leftmost column.  In the “bad table” example you’ll see there is an error message, as the columns are not in the right order.

This is one of the major drawback of VLOOKUP, and for this reason it’s highly recommended to use INDEX MATCH MATCH instead of VLOOKUP.

 

Step 2: Tell the function what to lookup

In this step we tell Excel what to look for.  We start tying the formula “=VLOOKUP(“ and then select the cell that contains the information we want to lookup. In this case, it’s the cell that contains “Bananas”.

 

vlookup step 2

 

Step 3: Tell the function where to look

In this step we select the table where the data is located, and tell excel to search in the leftmost column for the information we selected in the previous step.

For example, in this case we highlight the whole table from column A to column C.  Excel will look for the information we told it to lookup in column A.

 

 

Step 4: Tell Excel what column to output the data from

In this step we need to tell Excel which column contains that data that we want to have as an output from the VLOOKUP.  In order to do this, Excel needs a number that corresponds to the column number in the table.

In our example, the output data is located in the 3rd column of the table, so we enter the number “3” in the formula.

 

vlookup step 4

 

Step 5: Exact or approximate match

This final step is to tell Excel if you’re looking for an exact or approximate match by entering “True” or “False” in the formula.

In our VLOOKUP example, we want an exact match, so we type “True” in the formula.

An approximate match would be useful when looking up an exact figure that might not be contained in the table, for example the number 2.9585. In this case, Excel will look for the number closest to 2.9585, even though that number is not contained in the dataset.  This will help prevent errors in the VLOOKUP formula.

Learn how to do this step by step in our Free Excel Crash Course!

 

VLOOKUP in financial modeling and financial analysis

VLOOKUP formulas are often used in financial modeling and other types of financial analysis to make models more dynamic and incorporate multiple scenarios.

For example, imagine a financial model that included a debt schedule, and the company had three different scenarios for the interest rate: 3.0%, 4.0% and 5.0%.  A VLOOKUP could look for a scenario, low, medium or high and output the corresponding interest rate into the financial model.

 

vlookup financial modeling example

 

As you can see in the example above, an Analyst can select the scenario they want and have the corresponding interest rate flow into the model from the VLOOKUP formula.

Learn how to do this step by step in our Free Excel Crash Course!

 

Additional resources

This has been a guide to the VLOOKUP function, how to use it, and how it can be incorporated into financial modeling in Excel.

Even though it’s a great function, as mentioned above, we highly recommend using INDEX MATCH instead, as this combination of functions can search in any direction, not just left to right.  To learn more, see our guide to INDEX MATCH.

To keep learning and developing your skills, check out these additional resources: