ExcelExplained

How to use VLOOKUP function in Excel

This is by far one of the most used Excel function and understanding how to use VLOOKUP will yield great benefits. It was created to lookup and retrieve data from a specific column in a table. Approximate and exact matching are supported, and wildcards (* ?) can be used for partial matches.

How to use VLOOKUP function

VLOOKUP function retrieves a value from a table by matching the criteria in the first column. The lookup columns (the columns from where we want to retrieve data) must be placed to the right.

How to use VLOOKUP function in Excel

So, what is the syntax for the VLOOKUP function?
=VLOOKUP(lookup_value,table_array,col_index_num,match_type)

Using the function implies setting 4 arguments:
lookup_value: the value you are trying to find in the first column of the table
table_array: the table containing the data
col_index_num: the column number in the table that contains the value you want to return
match_type: true = approximate match; false = exact match

Now let’s look at how to use VLOOKUP with an example. In the  formula from cell F2 the lookup_value is cell E2, table_array is range B4:F17, col_index_num is set to 5, and the match type is set to FALSE (exact match). What is important to remember is that the column number refers to the Xth column of the table. In this example I wanted to retrieve the Annual Salary for the employee with the ID number 4679, which will return the value $31,000.

Things to remember about VLOOKUP

VLOOKUP searches tables where data is organized vertically. This means that each column must contain the same type of data and each row represents a new database record. The 4th column of our example contains the Division where the employee is working. However, each row corresponds to the division of a different employee.

If you are looking to retrieve data organized horizontally you can use HLOOKUP (works the same way as VLOOKUP).

VLOOKUP only looks right

I strongly believe that this is the biggest limitation of the VLOOKUP function. In order to work properly you need to create a table where the first column (the first from left to right) contains the lookup_value. This means that the data you want to retrieve can appear in any column to the right, but the lookup_value must be in the first table column.

Looking at our example, the ID is the lookup_value and VLOOKUP will search for it in column B, which is the first column in our table_array.

VLOOKUP retrieves data based on column number

Whenever you use VLOOKUP you have to provide the column number from where you want to retrieve data. Our table_array contains 5 columns. You can rewrite the VLOOKUP function based on the information you want to retrieve:
=VLOOKUP(E2,B4:F17,2,FALSE) – First Name of the employee
=VLOOKUP(E2,B4:F17,3,FALSE) – Last Name of the employee
=VLOOKUP(E2,B4:F17,4,FALSE) – Division of the employee

What you need to remember is that adding new columns to your table will change the order number in your table. As a result, this will break your existing VLOOKUP formulas.

VLOOKUP is not case sensitive

VLOOKUP cannot distinguish between different cases and treats both uppercase and lower case in the same way. For example “GOLD” and “gold” are considered the same.

VLOOKUP supports approximate and exact match

When using VLOOKUP, exact match is most likely your best approach. Take our employee table for example. The ID is unique for each employee and it wouldn’t make sense to approximate it.

There are however times when using approximate match makes sense. Let’s look at another example.

I have created a simple fee structure, based on the transaction amount. When we apply the formula for a transaction equal to $4,800, VLOOKUP will look for the best match. Since it cannot find the value we have provided, VLOOKUP will search until it finds a value higher than cell B3 (in our case cell B10). Then, it will go back to the previous largest value, cell B9, and retrieve the corresponding fee (4.00%).

How to use VLOOKUP with approximate match

Whenever using approximate match your data must be sorted in ascending order by lookup value (in our case the Transaction Amount). Otherwise, VLOOKUP will not retrieve the correct data.

Note: If you omit the match_type argument Excel will use approximate match by default, but will retrieve the exact match if one exists.

If you have additional questions on how to use VLOOKUP please let me know by posting a comment.

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.