November 14, 2021

You may be wondering "what is VLOOKUP function 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.

I'll try to keep this article as simple as possible. Think of it as VLOOKUP for dummies 🙂

VLOOKUP is used to search and retrieve data from a specific column in a table. For example, you can look up the price of a product in a database, or find an employee name based on their employee ID.

Approximate and exact matching is supported, and wildcards (* ?) can be used for partial matches.

The first questions I hear from people are "how does VLOOKUP work?" and "how to do VLOOKUP?" Well, the function retrieves a lookup value from a table array 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.

It's important to understand the VLOOKUP function syntax. There are 4 arguments: `=VLOOKUP(lookup_value, table_array, col_index_num, match_type)`

**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.

Let's look at how the VLOOKUP function works 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 in understanding how to write a VLOOKUP formula is to identify which data you need to retrieve. In this case, it is the Annual Salary of the employee with the ID number '4679'. This is the **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.

Here's a quick video from Microsoft that demonstrated how to write a VLOOKUP formula.

To write the VLOOKUP formula in cell F2 follow these steps:

- Type =VLOOKUP(
- Use cell
`E2`

as the lookup value - Select the range of cells
`B5:F17`

which defines the table where the data is stored (the table array argument) - Insert
`5`

as the col_index_number argument as we are looking to retrieve data from the 5th column from our table - Choose Exact match for the match_type parameter. Remember, this corresponds to
`FALSE`

in our formula. - Type ) and then press Enter to complete the VLOOKUP formula.

Excel will return the Annual Salary of $31,000, which is earned by Ava Williams from Accounting, employee ID number '4679'.

This is pretty much all you need to know about VLOOKUP. The following sections of this article describe different Excel error messages that you can encounter when using VLOOKUP.

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.

Sometimes, when you use the VLOOKUP function in Excel, your formula might return the #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 article.

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 `=IFNA(VLOOKUP(E2, B5:F17, 5, FALSE), "Custom message")`

Alternatively, if your version of Excel doesn't include the IFNA function you can achieve the same result using IF + ISNA. The formula is `=IF(ISNA(VLOOKUP(E2, B5:F17, 5, FALSE)), "Custom message", VLOOKUP(E2,B5:F17, 5, FALSE))`

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.

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(E2, [VLOOKUP.xlsx]VLOOKUP!B4:F17, 5, FALSE)`

`[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).

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

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.

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

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.

As you become better at working in Excel, you will naturally feel the need to create more complex spreadsheets that better match your needs. This includes nesting functions.

One of the most used combos is nesting a VLOOKUP formula inside an IF statement so that the VLOOKUP formula is only triggered if certain conditions are met.

For example, there may be no point in looking for a sales bonus if the overall budgeted target is not met.

If you are not familiar with IF formulas, I strongly encourage you to read the article I wrote on how to use IF function in Excel.

My recommendation is to play around with as many functions as possible. It's the only way you will truly improve your Excel skills. And remember, if you need VLOOKUP help or if you have additional questions on how to use VLOOKUP please let me know by posting a comment.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.

View coursesExcel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.

Copyright © 2017-2022 ExcelExplained.com

how to find if i need to get price for respected to percentage ( If I Write Percentage from Second Column, Price must come as result from first column)

Hi Saht. Can you please be a little more specific with what you are trying to achieve? An example would be very helpful because it makes it easier for me to understand what you are trying to achieve.

Thanks For The Reply.

As I asked before more detailed here...

Name | Description | Number

Raj | Plumber | 780

John | Mechanic | 790

Abu | Welder | 890

if I Write "790", Then I need "Mechanic" or "John" as the result.

How can I modify vlookup for that or there is any other formula

VLOOKUP only looks to the right, which means that if you want to retrieve the Name or Description based on the value in column Number, then you will have to move the Number as the first column. Then, your header will become Number | Name | Description.

Alternatively, if you do not want to move the column Number you can use INDEX-MATCH. Check out this article as it also contains an Excel example file which can easily be adjusted in your case - https://excelexplained.com/how-to-use-index-and-match-functions-in-excel/

Keep in mind that number needs to be unique. Otherwise VLOOKUP or INDEX-MATCH will retrieve only the first match.