VLOOKUP Tutorial – How To Use VLOOKUP Function In Excel

This comprehensive VLOOKUP Tutorial explains what is VLOOKUP in Excel and how to use this function with the help of practical examples:

VLOOKUP is a filtering method available in Microsoft Excel which allows us to search for a value using a formula in a huge list of data.

In this tutorial, we will get to know what is Lookup, the Lookup formula, a detailed explanation of the parameters with examples, the difference between Exact and approximate matches, and How to use VLOOKUP from a different sheet and workbook.

We will also understand the reasons for N/A errors while working with this function.

=> Easy Excel Training Tutorials For ALL

What Is VLOOKUP In Excel

It is an Excel built-in function that will find and return a value in the column, which is associated with a row that matches the specified match criteria.

Confused? Let’s understand this with an example.

Suppose you have a huge list of employee details and you want to find the address of an employee with ID 1007. So VLOOKUP will return the value from the address column which is associated with a row that matches employee ID 1007.

VLOOKUP stands for Vertical Lookup. This function will always look for the value on the right side i.e. the function will look up for the values in the left-most column of the specified array and search for the match in the columns present on the right side.

VLOOKUP Formula

The ways to implement VLOOKUP in Excel are explained below.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let’s understand the parameters used in the formula one by one.

Lookup_value: This is a required field that contains the value that you wish to search in the first column of the table.

Table_array: This is a mandatory field in which the searching has to be performed. The Lookup_value uses the first column of this table.

col_index_num: This is the required integer value that specifies the details about the column number from which the data value has to be returned.

Range lookup: This is an optional argument that will tell the function whether to find an exact match or an approximate match.  The value of this argument can be TRUE or FALSE. TRUE refers to an Approximate match and FALSE refers to the EXACT match. The default value is True.

We will discuss more EXACT and APPROXIMATE matches later in this tutorial.

VLOOKUP Example

Before we start using this formula, we should first organize our data. Since VLOOKUP only looks at the data to the right side of the table, you have to make sure that the value you want to fetch is on the right of the table.

Examples of an organized table and a bad table are shown below.

Suppose the lookup value is in the Product ID column, then the product ID should be the left-most column.

Bad Table Example

This table will never give a correct result because the VLOOKUP function will look up towards the right and gives you a NA result.

Organised Table Example

This table has the look-up value towards the left-most column which works perfectly with the VLOOKUP formula.

Our table is ready, so let’s start using the VLOOKUP formula. Select any cell and hit =V and Excel will suggest you options, so select VLOOKUP.

Consider an example where we want to know the unit price for a cake.

Given below is the formula:

=VLOOKUP(12347,A2:D6,3,FALSE)

Result returns 352.34

Let’s understand the result

1st parameter we entered is 12347. VLOOKUP function will search for the product ID 12347 in the first column of the table.

Note: Non-numeric value has to be entered in double quotes like “Cake” while the numeric value can be specified without quotes as shown above.

2nd parameter is A2:D6 and this tells the function to look for the data to be fetched in this table range.

3rd Parameter is 3, which tells the function to search in column 3 and return the value which matches the criteria. So in our example search in the Unit price column.

4th parameter in our example is FALSE i.e. VLOOKUP is finding an EXACT match for the value of 12347. Since it found an exact match, it returns the value as 352.34.

Exact Match

As mentioned before if the 4th parameter is set to False then the VLOOKUP function will treat it as an exact match.

Consider this simple table which only has Unit Price and Product name to understand the EXACT and APPROXIMATE match. We have mentioned the VLOOKUP values in the D3 to D7.

Now let’s enter the formula mentioned below in E3 and drag the formula down to all the cells below till E7.

=VLOOKUP(D3,$A$3:$B$7,2,FALSE)

Note: More about $A$3:$B$7 will be discussed later in this tutorial.

The resulting table will be as shown below.

Since the first value matched exactly, the result is returned as Brownie. As all the other values do not match, the #N/A error is returned.

Approximate Match

If the 4th argument is True then VLOOKUP will enable an approximate match. The approximate match is enabled by default i.e. if you don’t specify the 4th argument then VLOOKUP will treat it as an approximate match.

Things to remember while dealing with the approximate match.

1. The lookup column has to be sorted in ascending order.
2. If the lookup value is the smallest value in the table array then the #N/A error is returned.

Using the same table, let’s rewrite the formula with a TRUE in the 4th argument.

The resulting table is shown below.

The first value matches exactly, hence Brownie is returned. For the 2^nd, 3^rd, and 4^th, the value returned is the closest match which is smaller than the lookup value entered.

For the last, the value entered is smaller than the values present in the table array, hence it gives the NA error.

Absolute Referencing

We often have the habit of reusing the data or formulas whenever necessary by using copy-paste method or by simply dragging the cell value to the cells present below.

In the above examples, we have used relative referencing i.e. using table array-like =VLOOKUP(12347,A2:D6,3,FALSE)

Now when you copy this formula to another cell, the table will automatically get adjusted by Excel and change the relative reference of the cell to the place you paste. This will cause a problem because the updated table array will not show the correct data.

In order to fix this, we have to use absolute referencing like prefix the row and column with a $ symbol as shown below

=VLOOKUP(D3,$A$3:$B$7,2,FALSE)

Let’s have a look at the results with and without using absolute reference.

VLOOKUP Between Two Worksheets

You could also find a value even when the table array is present on another sheet. Let’s write our above table in a different sheet, for example, Sheet2 in the range A1:B6.

Let’s create a table as shown below in Sheet 1 and Sheet 2

Sheet 1:

Sheet 2:

Given below is the formula to lookup the value 13456 in Sheet 2 and return the Unit Price. Go to Sheet 1, hit = on any cell, and enter the below VLOOKUP formula.

=VLOOKUP(13456, Sheet2!A1:B6, 2, FALSE)

Notice that we just have to add the sheet name i.e. Sheet2 before the table range along with an exclamation.

This will instruct the function to use the table array from A1:B6 present in a different Sheet named Sheet2.

Spaces In Sheet Name

Now try the above-mentioned technique with sheet name which has Spaces. Example: New Sheet.

Given below is the result. The reason is that there is a space in the sheet name.

If there is a space in the sheet name, then to make VLOOKUP understand, you need to wrap the name in a single quote as shown below.

=VLOOKUP(10251, ‘New Sheet’!A1:B6, 2, FALSE)

VLOOKUP From Another Workbook

As we have learned how to use this formula from another sheet, it’s time to see how to search for an item from another workbook.

Suppose you have outsourced the profit and loss calculation to an external organization and they store all the values in a different workbook. Now to fetch those values VLOOKUP function provides us an easy way with just a few changes in the original function.

Follow the below steps, to find value in different workbooks:

#1) Open both the workbooks, though it is not mandatory to open the workbook, it will be easier for beginners to create the formula.

For ease place both the workbooks arranged horizontally as shown below.

#2) Hit =VLOOKUP in the desired cell of 1^st workbook, for the first argument, select the cell, in our example, we will select 45212 product ID

For the 2nd argument, select the range in the other workbook. Click on the workbook, in our example VLOOKUP_ProfitLoss.xlsx, and select the cells from A2 : C6.

Notice that the formula is automatically updated with workbook and worksheet name

=VLOOKUP(A35,[VLOOKUP_ProfitLoss.xlsx]Sheet1!$A$2:$C$6,2,TRUE)

The 3^rd and the 4^th arguments should be selected as discussed before. In this example, the column number is 2 for profit, and let’s select an exact match.

The result is shown below.

Now if you close the VLOOKUP_ProfitLoss.xlsx workbook, the VLOOKUP function will show the full path in the formula as shown below.

Common Errors

The most common error you will encounter while working with VLookup is the #N/A error. This is displayed when your Excel cannot look for the value specified by you. There could be many reasons which trigger this error. Below mentioned are the scenarios

#1) N/A when using an approximate match

If you encounter an N/A error when the 4th argument is set to TRUE or is set to default, then the following could be the reason.

• If the lookup value you specified is the smallest in the table array.
• If the column used to lookup is not arranged in ascending order.

#2) N/A when using exact match

If you encounter an N/A error when the 4th argument is set to False then NA is caused because there is no match found in the table array.

#3) The Lookup column is not the first column

We have already discussed that this function always looks for the value to the right, hence the lookup column should always be the left-most column. If not, then it will throw a NA error.

Given below is the result when the VLOOKUP value column (Unit Price) is not the left-most.

#4) Numbers are formatted as text

When you import data from an external source, then at times, the Number field is formatted as text, which could be confusing to VLOOKUP. Hence it is always better to convert to a number before proceeding by selecting the number cells select cell, right-click, Format  -> Number, and click OK.

#Value Error

There is another kind of error called #Value error that occurs under the below-mentioned scenarios.

#1) Wrong path is specified

When you are taking the data from another workbook, it is mandatory to use the correct path as discussed above.

Example

=VLOOKUP(lookup_value, ‘[workbook name]sheet name’!table_array, col_index_num, FALSE).

If any of the above syntaxes is missed then a #value error will be displayed.

#2) Mismatch in column index argument

If you enter the value as 0 (zero) in the column index argument, then VLOOKUP will return the #Value error.

Note: If the column index is greater, then the table array specified # Ref error will be displayed.

Handle NA Errors

We have seen the reasons for the NA error to trigger but notice that the error message given by Excel is not user-friendly.

In order to enter the message you desire, you can use any of the following functions.

1. =IF(ISNA(VLOOKUP(D7,A3:B7,2,TRUE)), “Value Not Found”, VLOOKUP(D7,A3:B7,2,TRUE))
2. =IFERROR(VLOOKUP(D7, $A$2:$B$7, 2, FALSE), “Value Not Found”)
3. =IFNA(VLOOKUP(D7, $A$2:$B$7, 2, FALSE), “Value Not Found”)

When you use these formulas, instead of showing N/A, the message you have entered will be shown in the result.

Important Facts to Remember

#1) Always returns the first value

If you have a table array with the same value, then VLOOKUP will always return the first matching value in the column.

#2) VLOOKUP is case-insensitive

The VLOOKUP function has no distinction between lowercase and UPPERCASE characters. In the above example, you can see that there is no distinction between Chocolate and chocolate. Hence, the function will return the first found value irrespective of their case.

#3) Change in Column Count

If a new column was added or if a column is deleted from the table array, then the function stops working. The formula is highly dependent on the table array range and the column index number, hence any changes to the same will obviously cause the function to stop working as expected.

Notice that the value is returned as 0 after inserting a new column to the table, as the value of the table array is no more valid.

Frequently Asked Questions

Q #1) What is the formula for VLOOKUP?

Answer: The formula is as shown below.

=VLOOKUP(What value to look up, where should function look for it, the column number, return an Approximate or Exact match ).

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The first 3 parameters are required and the 4th parameter is optional.

Q #2) What is VLOOKUP in Excel?

Answer: VLOOKUP in Excel is a built-in function that will find and return a value in the column, which is associated with a row, that matches the specified match criteria.

Example:

You are searching for the phone number of an employee with Employee ID 765 in a huge list of employee details.

So VLOOKUP function will find and return the value from the phone number column which is associated with the row that matches the employee ID 765.

Q #3) What is the difference between an exact match and an approximate match table lookup?

Answer: Exact and Approximate match is the 4th parameter in the VLOOKUP formula. An exact match is enabled when the value is FALSE.

The approximate match is enabled by default, so if you don’t enter any value for the 4th argument then, it treats it as an Approximate match. You can also enter TRUE to enable an Approximate match.

The exact match will look for the exact match and throw an NA error when the match is not found. An approximate match will return the closest value which is smaller than the lookup. If the lookup value you entered is the smallest then the NA error will be triggered.

Q #4) Why do we use 0 in VLOOKUP?

Answer: We can use 1 or 0 instead of True or False as the value for the 4th argument. VLOOKUP treats 1 as True (Approximate match) and 0 as False (Exact match).

In the below picture you can see that we have used 0 instead of False as the 4th argument.

Q #5) How do I do a VLOOKUP from two Excel workbooks?

Answer: You can use the formula as shown below, where the workbook with table array is mentioned in square bracket [ ] followed by the sheet name in which the table array is presently followed by! mark, followed by the row and column value.

=VLOOKUP(A35,[VLOOKUP_ProfitLoss.xlsx]Sheet1!$A$2:$C$6,2,TRUE)

The above formula will work if both the workbooks are open. If not then you have to write the full path of the workbook as given below.

=VLOOKUP(A35,‘C:\Users\Admin\[VLOOKUP_ProfitLoss.xlsx]Sheet1’!$A$2:$C$6,2,TRUE)

The rest of the argument remains the same as normal VLOOKUP.

Conclusion

In this tutorial, we have learned all the necessary details that are required to start using VLOOKUP in Excel. It is one of the best ways to find value in a huge list of data.

We saw how to use lookup when the table is in another sheet of the same workbook and also when the table is present in another workbook. We explored the types of errors that we can encounter while working with the VLOOKUP in Excel along with the reason for error occurrence.

We have also seen how to make VLOOKUP show the message we entered instead of NA when the desired result is not found.

=> Exclusive Excel Training Tutorial Series