You may be wondering “what is VLOOKUP and why should I bother learning it?” Well, as it turns out, this is by far one of the most used Excel functions, 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 is supported, and wildcards (* ?) can be used for partial matches.
How to use VLOOKUP function in Excel
The first question I hear is “how does VLOOKUP work?” Well, the 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 VLOOKUP syntax?
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 (the Excel lookup table)
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
I strongly recommend that you download the free Excel VLOOKUP example file as this VLOOKUP tutorial will be based on the data provided inside.
Bạn đang xem: How to use VLOOKUP in Excel (2020)
How to do a VLOOKUP in Excel
Let’s look at how to use VLOOKUP step by step using an easy example. When you want to write your VLOOKUP formula you need to follow the four-step process:
- Identify which is the value that you want to use (finding the lookup_value);
- Understand for which data to perform the VLOOKUP (selecting the table_array);
- Select which info you want to retrieve (selecting the col_index_num);
- Determine if you need to use an exact match or an approximate match (setting the match_type).
The first step is to understand which data we want to retrieve. In this case, it is the Annual Salary of the employee with the ID number ‘4679’. This is our lookup_value argument. As I mentioned before, this value needs to exist in the first column of your table. VLOOKUP performs the search in the first column and retrieves the info from whichever column you specify to the right.
The second step is to select the data where this info is available. In the image above example, this is table B5:F17 and this range is the table_array argument.
The third step is to check the column number from where we want to retrieve the info. Keep in mind that this refers to the number of columns from the table_array range and not the Excel column number. This is important! Since we need the Annual Salary (which is stored on column F) we will use 5 as col_index_num argument.
The final step is to choose Exact match or Approximate match. Because we are looking for a specific employee ID we need to use Exact match. I can safely say that probably around 95% of your VLOOKUP formulas will use Exact match. Since the match_type parameter is optional please remember that Excel uses Approximate match by default. If you want to be on the safe side I strongly recommend that you always input a value for your match_type argument.
Now, to bring it all together you can write the following formula in cell F2:
=VLOOKUP(E2,B5:F17,5,FALSE) and Excel will return the Annual Salary of $31,000, which is earned by Ava Williams from Accounting, employee ID number ‘4679’.
VLOOKUP #NA error message
What does #N/A error message mean in Excel? Well, N/A stands for Not Available and Excel is telling you that the value you are looking for could not be found. This is most common with VLOOKUP and INDEX-MATCH using exact match type since those functions work in a similar way.
Whenever the #N/A error message shows in your formulas, the first thing you should do is check your data manually. Sometimes there are spaces before or after the string you are looking for. In Excel “Apples” and “Apples ” are not matching strings and your vlookup formula will return an error message. Clean your data and check again if your formula is working properly.
You should also pay close attention to your table_array range. Since a VLOOKUP formula is usually used for comparing large data sets, if you are copying your formula across multiple rows you need to use the absolute reference. This way, your table_array range won’t change.
The image below shows how copying a formula without absolute reference moves the table_array downwards, which will cause the vlookup formula to return a value not available error because the lookup value is no longer inside the table_array.
If you want to learn how to ignore and hide #N/A error in Excel then please continue reading. The following chapter teaches you two methods of hiding the #N/A vlookup error message.
Xem thêm: Top 3 ứng dụng của Hidro trong đời sống
VLOOKUP #REF error message
Sometimes your vlookup formula returns #REF! error message. There are two possible reasons why your vlookup formula is not working: you have invalid range references or a cell or range referenced in your formula has been deleted.
The most common fix is to check your col_index_num parameter. Most likely the value is higher than the total number of columns in your table.
In the formula below, the col_index_num is 6 but there are only 5 columns in the table_array. This will get you a #REF! error message.
If your formula actually contains the string #REF! in the formula bar, then it means you have deleted either the lookup_value or the table_array. Cutting and pasting values in cells that are linked to your vlookup formulas will trigger the same error message.
The image below shows a VLOOKUP #REF! error generated from cutting and pasting data to cell E2, which is the lookup_value.
If this doesn’t fix your vlookup #REF! error, please refer to my
#REF error in Excel
How to hide #N/A errors in your VLOOKUP formulas using IFNA or IF + ISNA
Whenever I teach someone to use VLOOKUP, I emphasize the fact that a VLOOKUP formula can trigger a lot of errors, but you only want to trap and set a customized error message when VLOOKUP returns the #N/A error. Why is that? Well, that’s because an #N/A error means that the value you are looking for was not found, but a #VALUE error means something completely different.
If you hide all errors behind a custom message you are doing yourself a huge disservice for two reasons.
Firstly, a function like IFERROR only hides the error but doesn’t fix it. Over time this will slow down your files considerably and decrease performance.
Secondly, if you messed up your VLOOKUP formula you need to know. If you use IFERROR you won’t realize that the formula is wrong and may think that the value you are looking for doesn’t exist, when in fact it does.
The good news is that Excel has introduced a new function called IFNA. This allows you to quickly display a custom message if your formula returns an #N/A error. The Excel formula to achieve this in our example can be written as
Alternatively, if your version of Excel doesn’t include the IFNA function you can achieve the same result using IF + ISNA. The formula is
In case you want to learn how to fix your Excel formulas I wrote a detailed article on
Excel error messages
. It includes a detailed guide on how to troubleshoot and fix #NULL! error, #REF! error, #DIV/0! error, #NAME? error, #N/A error, #NUM! error, #VALUE! error, and ##### for most common functions.
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 the 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, the 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 the 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 need VLOOKUP help or if you have additional questions on how to use VLOOKUP please let me know by posting a comment.
Chuyên mục: Kiến thức