tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Excel Functions

(Scroll to see more)

Share this page:

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 to 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)}

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:

=IF(ISNA(VLOOKUP(D14, $Q$1:$S$10, 3, FALSE)), "Not Found", VLOOKUP(D14, $Q$1:$S$10, 3, FALSE))

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.