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.
So, what is the syntax for the VLOOKUP function?
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.
Performing a VLOOKUP from another sheet (or multiple sheets)
If you need to perform a vlookup from another sheet or file, I have good news for you: it’s just as easy. All you need to do is create your VLOOKUP formula like you normally would, but define the
table_array parameter to point to your desired sheet (or file). In this vlookup tutorial I will show you how to perform an Excel vlookup for employee id, when the database containing the employees is located in another file.
Let’s look at the previous example where we had a list of employees stored in a sheet named VLOOKUP, which was part of the example file called VLOOKUP.xlsx. The new file will point to the same table_array defined by cells B4:F17. However, the formula will look different because Excel will insert the file name and sheet in our table_array. When performing a VLOOKUP from another file, the formula we had in the previous example needs to be written as:
[VLOOKUP.xlsx] is telling us which file we have linked in our VLOOKUP formula, while
VLOOKUP!B4:F17 represents the sheet from VLOOKUP.xlsx file which contains the selected table_array
B4:F17. Don’t worry about the complicated syntax. Excel will create all the references automatically when you select a range from another file (or sheet).
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%).
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.