tech on the net

MS Excel: VLOOKUP Function (WS)

Learn how to use the Excel VLOOKUP function with syntax and examples. Notice the importance of the final parameter in returning the correct results.

Description

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

Syntax

The syntax for the Microsoft Excel VLOOKUP function is:

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

Parameters or Arguments

value is the value to search for in the first column of the table_array.

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

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

not_exact_match is 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_array, 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

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

  • Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

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 you would use the VLOOKUP function as a worksheet function in Microsoft Excel:

Microsoft Excel

Based on the spreadsheet above:

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

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

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.

The second parameter in the VLOOKUP function is the table_array 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.

The third parameter is the index_number which is set to 2. This means that the second column in the table_array is where we will find the value to return. Since the table_array 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).

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

=VLOOKUP(10248, A1:B6, 2, FALSE) would return #N/A
=VLOOKUP(10248, A1:B6, 2, TRUE) would return "Apples"

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

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.

Microsoft Excel

Based on the spreadsheet above:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)), 0, VLOOKUP(E2,$A$2:$C$5,3,FALSE)) would return 0

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.

Microsoft Excel

Based on the spreadsheet above:

=IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)), "No", "Yes") would return "No"
=IF(ISNA(VLOOKUP(A5,$D$2:$D$185,1,FALSE)), "No", "Yes") would return "Yes"

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"?

Answer: This can be done with a formula that utilizes a combination of the Index function, Small function, Row function (all in an array formula).

Microsoft Excel

If you wanted to return the quantity value for the second occurrence of apple, you would use the following array formula:

=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),2)

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),2)}

If you wanted to return the quantity value for the third occurrence of apple, you would use the following array formula:

=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),3),2)

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),3),2)}

If you wanted to return the bin # for the second occurrence of apple, you would use the following array formula:

=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),3)

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),3)}