VLOOKP Function in Excel (with examples), using Exact and Approximate Matches, Error (#N/A) handling, …

 

 

Related Links:

1. Left Lookup with VLookup Excel function.

2. Left Lookup, with Index, Match & Offset Excel Functions.

3. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value.

4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value.

 

---

 

VLOOKP Function:

The excel VLOOKUP function looks-up a specified value in the first column of the table array and returns a corresponding value in the same row from a specified column. This is a vertical lookup wherein the lookup value and the corresponding values are located in columns.

 

Syntax: VLOOKUP(value_to_lookup, table_array, column_number, exact_match)

 

1. value_to_lookup is the value to search or lookup in the first column of the table_array and should not be smaller than the smallest value in the first column else the function returns the error value of #N/A.

 

2. table_array is two or more columns of data, wherein text values are not considered case-sensitive.

 

3. column_number is the column number in the table_array from which the corresponding value is returned, starting from 1. Entering a number greater than the number of columns in the table_array will return the error value of #REF!.

 

4. exact_match (optional argument) is a logical value which determines whether the function will search to find an exact match or an approximate match for the value_to_lookup:

(i) if omitted or TRUE, then while searching in the first column of the table_array, if an exact match is not found it searches for the next largest value which is less than the value_to_lookup. In this case the values in the first column should be sorted in ascending order.

(ii) if FALSE, only the exact value_to_lookup is searched for and the first column need not be sorted. On not finding an exact match the function returns the error value of #N/A.

 

5. If the value_to_lookup is a numerical or date value, ensure that the data in the first column of the table_array is not formatted as text. Wildcard characters of question mark (?) and asterisk (*) can be used if the value_to_lookup is text and an exact match is being searched for.

 

—————————————————————————————————————————- 

 

Example 1 – Using an Approximate Match:

 

=VLOOKUP(17,A2:C10,3)  [Formula]

 

The formula returns the name “Hardy”. (Refer Table 1)

 

Using an approximate match, searches for the value 17 in column A, finds the largest value less than or equal to 17 in column A which is 14, and then returns the value from column C in the same row, viz. “Hardy”.

—————————————————————————————————————————–

 

Example 2 – Using an Exact Match:

 

=VLOOKUP(22,B2:C10,2,FALSE)  [Formula]

 

The formula returns the name “Humpty”. (Refer Table 2)

 

Using an exact match, searches for the value 22 in column B, and then returns the first occurrence value from column C in the same row, viz. “Humpty”.

—————————————————————————————————————————  

 

Example 3 – Error (#N/A) Handling in Using VLOOKUP

 

 

1. Error (#N/A) in Approximate Match:

 

=VLOOKUP(1,A2:C10,3,TRUE)  [Formula]

 

The formula returns an error (#N/A) value. (Refer Table 3)

 

Using an approximate match, searches for the value 1 in column A. Because 1 is less than the smallest value in column A, an error (#N/A) is returned.

 

 

2. Error (#N/A) in Exact Match:

 

=VLOOKUP(13,A2:C10,2,FALSE)  [Formula]

 

The formula returns an error (#N/A) value. (Refer Table 4)

 

Using an exact match, searches for the value 13 in column A. Because there is no exact match in column A, an error (#N/A) is returned.

 

3. Text vs Number:

When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value. In this case, convert numbers stored as text to numbers.

 

When Lookup_value is a number and Table_array is text

 

=VLOOKUP(15,B2:C10,2,FALSE)  [Formula]

 

The formula returns an error (#N/A) value. (Refer Table 5)

 

This Formula is searching for the number 15 in column B, but the value 15 in column B (cell B9) is entered as text, hence an error (#N/A) is returned.

 

 

In this case either convert the data (cell B9) into numbers, or use the following variation which converts Lookup_value in the formula to text:

 

– =TEXT(15.145,”0″) converts number 15.145 to text “15”, =TEXT(15.145, “0.0”) converts number 15.145 to text “15.1”, =TEXT(15.145,”0.00″) converts number 15.145 to text “15.15”; =TEXT(15.145, “0.000”) converts number 15.145 to text “15.145”.

 

– The formula =VLOOKUP(TEXT(15, “0”), B2:C10,2, FALSE) will return “Juliet”. Number 15 is converted to text with TEXT(15,”0″). (Refer Table 6)

 

 

– The formula =VLOOKUP((15&””), B2:C10, 2, FALSE) will return “Juliet”. Number 15 is converted to text with =(15&””). (Refer Table 7)

 

 

When Lookup_value is text and Table_array are numbers

 

=VLOOKUP(B12,B2:C10,2,FALSE)  [Formula]

 

The formula returns an error (#N/A) value. (Refer Table 8)

 

This Formula is searching for the text 18 (cell B12 contains text “18”) in column B, but the value 18 in column B (cell B5) is entered as number, hence an error (#N/A) is returned.

 

 

 

In this case either convert the data (cell B5) into Text, or use this variation which converts Lookup_value in the formula to number:

 

– The double unary minus (–) will convert text to a number. The (–) coerces TEXT numbers to NUMERIC numbers. The formula =VLOOKUP(–B12, B2:C10, 2, FALSE) will return “Dumpty”.  (Refer Table 9)

 

 

– When numbers are in text format, use VALUE(text) to convert to a number. The Excel Value function, converts a text string that represents a number, to a number. The formula =VLOOKUP(VALUE(B12), B2:C10, 2, FALSE) will return “Dumpty”.  (Refer Table 10)

 

4. Text vs Dates:

 

When Lookup_value is a ‘real’ date and Table_array is text

 

=VLOOKUP(B14, B2:C10, 2,FALSE)  [Formula]

 

The formula returns an error (#N/A) value.  (Refer Table 11)

 

This Formula is searching for the ‘real’ date (4/19/2011) in column B, but the value (4/19/2011) in column B (cell B1) is entered as text, hence an error (#N/A) is returned.

 

 

 

In this case either convert the data (cell B1) into ‘real’ date, or use the following variation which converts Lookup_value in the formula to text:

 

– The formula =VLOOKUP(TEXT(B14, “m/dd/yyyy”), B2:C10, 2, FALSE) will return “Jack”. The ‘real’ date (4/19/2011) is converted to text format with: TEXT(B14, “m/dd/yyyy”). (Refer Table 12)

 

 

When Lookup_value is text and Table_array contains ‘real’ date

 

=VLOOKUP(B13,B2:C10,2,FALSE)  [Formula]

 

The formula returns an error (#N/A) value. (Refer Table 13)

 

This Formula is searching for the text “3/10/2010” (cell B13 contains text “3/10/2010”) in column B, but the value “3/10/2010” in column B (cell B10) is entered as date, hence an error (#N/A) is returned.

 

 

 

In this case either convert the data (cell B10) into Text, or use this variation which converts Lookup_value in the formula to date:

 

– The double unary minus “–” will coerce TEXT dates to DATE format. If B13 contains text which looks like a date, the double unary minus will convert it to a ‘real’ date. The formula =VLOOKUP(–B13,B2:C10, 2,FALSE) will return “Archie”. (Refer Table 14)

 

 

 

5. Remove Hidden Characters and Spaces

 

When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ‘ or ” ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. You can use CLEAN and TRIM functions to ensure this.

 

=VLOOKUP(B15,B2:C10,2,FALSE)  [Formula]

 

The formula returns an error (#N/A) value. (Refer Table 15)

 

The problem will arise in case there are hidden characters or spaces, either in the Lookup_value or the Table_array. In this case, the hidden characters or spaces will need to be removed from wherever they are present. To remove hidden characters, use “=CLEAN(text)”, to remove spaces, use “=TRIM(text)” and to remove both hidden characters and spaces, use “=TRIM(CLEAN(text))”.

 

This Formula is searching for the string ”   April” [cell B15 has 3 leading spaces, which can also be determined with LEN(B15) which will return 8 instead of the 5 character length of the string “April”] in column B, but the string “April” in column B (cell B6) has no hidden space, hence an error (#N/A) is returned.

 

 

In this case either eliminate the hidden spaces in the data (cell B6), or use the following variation which eliminates hidden spaces in the Lookup_value:

 

– The formula =VLOOKUP(TRIM(B15), B2:C10, 2,FALSE) will return “Laurel”. The hidden spaces in cell B15 have been eliminated with “TRIM(B15)”. (Refer Table 16)

 ——————————————————————————————————————————–

 

Example 4 – Using Vlookup with IFERROR, ISNA and IF:

 

=IFERROR(VLOOKUP(13,B2:C10,2,FALSE),”Not Found”)  [Formula]

 

The formula returns the string “Not Found”. (Refer Table 17)

 

if a formula evaluates to an error, ‘IFERROR function’ returns a value you specify, or else returns the result of the formula.

 

 

 

=IF(ISNA(VLOOKUP(13,B2:C10, 2,FALSE)=TRUE), “Not Found”, VLOOKUP(13, B2:C10,2, FALSE))  [Formula]

 

The formula returns the string “Not Found”. (Refer Table 18)

 

If there is a number 13 in column B, returns the value from column C in the same row, else displays the message “Not found”.

 

The ISNA function returns a TRUE value when the VLOOKUP function returns the #NA error value.

——————————————————————————————————————————  

 

Example 5 – Using Vlookup for Calculations:

 

 

=VLOOKUP(“Ha”, A2:D10, 3, FALSE)/VLOOKUP(“Ha”, A2:D10, 4, FALSE)  [Formula]

 

The formula returns the value 77.8%. (Refer cell E3)

 

Calculates the %age marks of Hardy by dividing his marks by max marks, and returns 77.8%. It may be noted that the result format (viz. cell E3, Table 19a) is “Percentage”, else the formula would have to be multiplied by 100 to calculate %age.

 

 

 

=VLOOKUP(“Ro”, A2:D10,3, FALSE)/VLOOKUP(“Ro”, A2:D10, 4, FALSE)-VLOOKUP(“Ha”, A2:D10, 3, FALSE)/VLOOKUP(“Ha”, A2:D10, 4, FALSE)  [Formula]

 

The formula returns the value 2.2% (refer cell E7, Table 19b), which is the difference in %ages of marks of Romeo and Hardy.

 

 

 

=(VLOOKUP(“Ha”, A2:D10, 3, FALSE)-5)/VLOOKUP(“Ha”, A2:D10,4, FALSE)  [Formula]

 

The formula returns the value 72.2% (refer cell E10, Table 19c), which would have been Hardy’s %age if he had got 5 marks less.

—————————————————————————————————————————–  

 

Example 6 – Using Vlookup with Concatenation:

 

=VLOOKUP(F2&”, “&G2,A2:D10,4,FALSE)  [Formula]

 

The formula returns the marks as 75 in cell F4, Table 20.

 

To vlookup how many marks Jill has got in Maths, create a helper column to the extreme left (viz column A) which concatenates values of column B and column C.

 

In the VLOOKUP formula, combine/concatenate the Name and Subject as the Lookup_value, in the same manner as concatenated in column A [ie. including a comma and space after comma viz. (F2&”, “&G2)].

—————————————————————————————————————————-  

 

Example 7 – Using Vlookup for a Range of Values:

 

 

=VLOOKUP(B4,Grades,2)  [Formula]

 

The formula returns “VI” in cell H2, Table 21a.

 

The cell range ($E$2:$F$9) is named “Grades”, from which grades are calculated. The Marks column is in ascending order in which Lookup_value (viz B4) is searched.

 

The Formula returns the grade of Humpty, by doing a Vlookup on his Marks (cell B4 – which is the Lookup_value) in the Table_array (which is named ‘Grades’): returned Grade is VI.

 

 

 

=”Returned Grade is ” & VLOOKUP(B4,Grades,2)  [Formula]

 

Formula returns the string “Returned Grade is VI”, in cell H7, Table 21b.

 

Concatenates Text (“Returned Grade is “) with the grade as above. Please note the space after “is” in the Text.