Microsoft

Web Development

Language

More

MS Excel: VLOOKUP Function (WS)

This Excel tutorial explains how to use the Excel VLOOKUP function with syntax and examples. How to handle errors such as #N/A and retrieve the correct results is also discussed.

Description

The VLOOKUP function performs a vertical lookup by searching for a value in the left-most column of the table and returning the value in the same row in the index_number position.

Syntax

The syntax for the VLOOKUP function in Microsoft Excel is:

VLOOKUP( value, table, index_number, [not_exact_match] )

Parameters or Arguments

value

The value to search for in the first column of the table.

table

Two or more columns of data that is sorted in ascending order.

index_number

The column number in table from which the matching value must be returned. The first column is 1.

not_exact_match

Optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLOOKUP function will look for the next largest value that is less than value. If this parameter is omitted, the VLOOKUP function returns an approximate match.

Note:

If index_number is less than 1, the VLOOKUP function will return #VALUE!.

If index_number is greater than the number of columns in table, the VLOOKUP function will return #REF!.

If you specify FALSE for the not_exact_match parameter and no exact match is found, then the VLOOKUP function will return #N/A.

Applies To

Type of Excel Function

The VLOOKUP function can be used in Microsoft Excel as the following type of function:

Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel VLOOKUP function examples and explore how to use the VLOOKUP function as a worksheet function in Microsoft Excel:

Based on the spreadsheet above:

=VLOOKUP(10251, A1:B6, 2, FALSE)
Result: "Pears"

This VLOOKUP example would return the value of Pears. Let's take a closer look why.

First Parameter

The first parameter in the VLOOKUP function is the value to search for. So in this example, the VLOOKUP is searching for the value of 10251.

Second Parameter

The second parameter in the VLOOKUP function is the table which is set to the range of A1:B6. The VLOOKUP uses the first column in this range (ie: A1:A6) to search for the value of 10251.

Third Parameter

The third parameter is the index_number which is set to 2. This means that the second column in the table is where we will find the value to return. Since the table is set to A1:B6, the corresponding return value will be in B1:B6 (ie: second column as specified by the index_number of 2).

Fourth Parameter

Finally and most importantly is the fourth or last parameter in the VLOOKUP. In our example, it is set to FALSE. This means that you need to find an EXACT match for the value of 10251. We do not want to find a "close" match, but an EXACT match!! So if 10251 is not found in the range of A1:A6, then the VLOOKUP function should return #N/A.

Since the VLOOKUP is able to find the value of 10251 in the range A1:A6, it returns the corresponding value from B1:B6 which is Pears.

Importance of Fourth Parameter

Let's further explore the importance of specifying TRUE vs FALSE for the last parameter in the VLOOKUP function.

So say we are looking for the Order ID of 10248, but as you can see, it is not in the range of A1:A6 in the spreadsheet above. Let's write our VLOOKUP formula with FALSE as the final parameter and another VLOOKUP formula with TRUE as the final parameter and see what happens.

The first VLOOKUP formula has FALSE specified as the final parameter. This means that the VLOOKUP is looking for an exact match for 10248. Since the value 10248 does not exist in the range A1:A6, the VLOOKUP function returns #N/A.

The second VLOOKUP formula has TRUE specified as the final parameter. This means that if an exact match if not found, the VLOOKUP function will look for the next largest value that is less than 10248. Now what does this mean to us?

First of all, it means that the data in A1:A6 MUST BE SORTED IN ASCENDING ORDER because the VLOOKUP is going to return the next largest value for 10248 and then stop searching. So if your data is not sorted in ascending order, you are going to get some really strange results.

Secondly, it means that the VLOOKUP function will find order 10247 as the approximate match. And therefore, return Apples as the result (the corresponding value from B1:B6).

Table in another sheet

Quite often we are asked the question, "What is an example of a VLOOKUP when the table is on another sheet?"

To answer that question, let's modify our example above and assume that the table is on Sheet2 in the range A1:B6.

We could rewrite our example as follows:

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

By preceding the table range with the sheet name and an exclamation mark, we can update our VLOOKUP to reference a table on another sheet.

Table in another sheet with spaces in sheet name

Let's throw in one more complication, what happens if your Sheet name contains spaces, then you will need to change the formula further. So let's take a look at this case...

Let's assume that the table is on a Sheet called "Test Sheet" in the range A1:B6, we would need to modify our formula as follows:

=VLOOKUP(10248, 'Test Sheet'!A1:B6, 2, FALSE)

By placing the sheet name within single quotes, we can accommodate a sheet name with spaces in our VLOOKUP function.

Absolute Referencing

Now it is important for us to mention one more mistake that is commonly made. When people use the VLOOKUP function, they commonly use relative referencing for the table range like we did in our examples above. This will return the right answer, but what happens when you copy the formula to another cell? The table range will be adjusted by Excel and change relative to where you paste the new formula. Let's explain further...

So if you had the following formula in cell G1:

=VLOOKUP(10248, A1:B6, 2, FALSE)

And then you copied this formula from cell G1 to cell H2, it would modify the VLOOKUP formula to this:

=VLOOKUP(10248, B2:C7, 2, FALSE)

Since your table is found in the range A1:B6 and not B2:C7, your formula would return erroneous results in cell H2. To ensure that your range is not changed, try referencing your table range using absolute referencing as follows:

=VLOOKUP(10248, $A$1:$B$6, 2, FALSE)

Now if you copy this formula to another cell, your table range will remain $A$1:$B$6.

How to Handle #N/A Errors

Finally, let's look at how to handle instances where the VLOOKUP function does not find a match and returns the #N/A error. In most cases, you don't want to see #N/A but would rather display a more user-friendly result.

For example, if you had the following formula:

=VLOOKUP(10248, $A$1:$B$6, 2, FALSE)

Instead of displaying #N/A error if you do not find a match, you could return the value "Not Found". To do this, you could modify your VLOOKUP formula as follows:

This new formula will use the ISNA function to test if the VLOOKUP returns a #N/A error. If the VLOOKUP returns #N/A, then the formula will output "Not Found". Otherwise, it will perform the VLOOKUP as before.

This is a great way to spruce up your spreadsheet so that you don't see traditional Excel errors.

Frequently Asked Questions

Question: In Microsoft Excel, I'm using the VLOOKUP function to return a value. I want to sum the results of the VLOOKUP, but I can't because the VLOOKUP returns a #N/A error if no match is found. How can I sum the results when there are instances of #N/A in it?

Answer: To perform mathematical operations on your VLOOKUP results, you need to replace the #N/A error with a 0 value (or something similar). This can be done with a formula that utilizes a combination of the VLOOKUP function, IF function, and ISNA function.

First, you need to enter a FALSE in the last parameter of the VLOOKUP function. This will ensure that the VLOOKUP will test for an exact match.

If the VLOOKUP function does not find an exact match, it will return the #N/A error. By using the IF and ISNA functions, you can return the Unit Price value if an exact match is found. Otherwise, a 0 value is returned. This allows you to perform mathematical operations on your VLOOKUP results.

Question: I have a list of #s in column A (lets say 1-20). There is a master list in another column that may not include some of the column A #s. I want a formula in column B to say (if A1 exists in the master list, then "Yes", "No"). Is this possible?

Answer: This can be done with a formula that utilizes a combination of the VLOOKUP function, IF function, and ISNA function.

First, you need to enter a FALSE in the last parameter of the VLOOKUP function. This will ensure that the VLOOKUP will test for an exact match.

If the VLOOKUP function does not find an exact match, it will return the #N/A error. By using the IF and ISNA functions, you can return a "Yes" value if an exact match is found. Otherwise, a "No" value is returned.

Question: Is there a simple way in Excel to VLOOKUP the second match in a column? So, for instance, If I had apple, pear, apple listed in the column (each word in a separate cell), would there be a way to look up the values to the right of the second "apple"?

Question: To automate a spreadsheet: I am trying to write a formula using a Lookup formula in F14 so that when a rock type (say sh for shale) is entered in D14 it will look up the rock type in Q1 thru Q10 and fill in F14 with cell format fill pattern from S1 thru S10. How do I get it to recognize the format pattern and copy it to F14?

Answer: You can do this with a VLOOKUP function as follows:

=VLOOKUP(D14, $Q$1:$S$10, 3, FALSE)

In this VLOOKUP example, the rock type that you want to look up is in cell D14, the lookup data is found in the range of $Q$1:$S$10. We've absolutely referenced the lookup range so that you can copy the formula to other cells without the range changing. The third parameter is set to 3 because we want the value returned from column S which is the third column in the range of $Q$1:$S$10. And the final parameter in the VLOOKUP is FALSE because we are only looking for an exact match.

With this formula if a match is not found, the VLOOKUP will return #N/A. If you would like to return a different value when there is no match, say "Not Found", then you could modify your VLOOKUP as follows:

This formula would return "Not Found" if there was not a match. Otherwise, it would return the appropriate value from S1 to S10.

Question:I want to do a VLOOKUP if a statement is true.

Example:

If cell A2 = cell F9, I want to do a virtual lookup depending on what is in cell E34, the function would return corresponding data from cells defined as "TEAM".

This is what I came up with:

=If(A2=F9),VLOOKUP(+E34,TEAM,1+1)

Answer: You are very close. Since we don't know how many columns are in your named range called "TEAM", we'll just assume that you want to return corresponding data from the second column in "TEAM". As such, you can do this with the VLOOKUP formula as follows:

=IF(A2=F9,VLOOKUP(E34,TEAM,2,FALSE))

In this example, VLOOKUP will be performed only if A2 is equal to F9.

The first parameter of E34 specifies the value to lookup. The second parameter uses the named range called "TEAM" which is where the lookup data is found. The third parameter of 2 will return corresponding data from the second column in the "TEAM" named range. The final parameter of FALSE means that we are only looking for an exact match.

I want to create a formula using the MATCH function nested within the INDEX function to retrieve the Class that was selected (by the x) in E4:F10. The MATCH function should find the row where the x is located and should be used within the INDEX function to retrieve the associated Class value from the same row within F4:F10.

Answer:Even though you can write this formula using a combination of the MATCH and INDEX functions, it is much easier and faster to perform this search using the VLOOKUP function, as follows:

=VLOOKUP("x",$E$4:$F$10,2,FALSE)

In this example, we are searching for the value "x" within the range E4:E10. When the value of "x" is found, it will return the corresponding value from F4:F10.