Six Reasons Why Your VLOOKUP or HLOOKUP Formula Does Not Work

by Patrick G. Matthews

Introduction

VLOOKUP is a tremendously useful function that allows you to "fetch" data from a specified rectangular range (the "lookup table"); this range can be on the same worksheet, a different worksheet in the same workbook, or even in a different workbook entirely.

In a VLOOKUP call, Excel will search in the left-most column of the lookup table for a sought-for value (or if an exact match is not specified, then the closest value not exceeding that sought-for value), and then return the value in the Nth column from that row in the lookup table.

However, from time to time, you may find that your VLOOKUP formula is returning an error, or is returning an incorrect value.  In my experience, there are six main causes for this:

This article will present a brief primer on VLOOKUP syntax, and then address each of these causes in turn.

Lastly, this article will also provide suggestions on how to handle errors returned by the VLOOKUP function.

Note: While VLOOKUP is the focus for this article, the same error conditions apply for the similar function HLOOKUP, which instead searches the first row of a lookup table, and then returns the value in the Nth row of the column where the match is found.  Since VLOOKUP is used more frequently than HLOOKUP, the examples in this article will use VLOOKUP.

Quick VLOOKUP Syntax Primer

The VLOOKUP function takes four arguments:

When writing a VLOOKUP formula, please keep the following in mind:

The image below shows a few basic examples of VLOOKUP formulae:


In that image:

Note: Elsewhere in this article, where I refer to a so-called "close match", I am referring to VLOOKUP's behavior when range_lookup is omitted or TRUE, and thus VLOOKUP is looking for the greatest value that is less than or equal to the value being sought.

Data Not Sorted

As noted above, if the optional fourth argument, range_lookup, is omitted or is TRUE, then VLOOKUP will look for a "close" match in the first column of the lookup table.  Specifically, VLOOKUP will look for the greatest value that is less than or equal to the value being sought, and then return the value in the Nth column of that corresponding row.

This will work, and allows the user to set up a lookup table to accommodate a single return value for a range of sought-for values.  However, as noted in the Quick VLOOKUP Syntax Primer section above, the data in the first column of the lookup table must be sorted ascending in order for this to work.

Consider the examples below:


In the first block, the items in the lookup table are not sorted correctly: code 7, violet, is listed out of order, and thus the formulae in G6 and G10 return an incorrect result.  In the second block, the formulae all return the correct results, because that lookup table lists the items in the correct order.

To avoid this error, if you are using VLOOKUP to find a "close" match, always make sure that your lookup table is sorted properly.

Note: In these examples, if we used FALSE for range_lookup, then the sort order of the lookup table would not have mattered, and all the formulae would have returned the correct results.  That is, when you set VLOOKUP to look for an exact match, the lookup table does not have to be sorted: it can be in any order, and VLOOKUP will return the correct result assuming an exact match exists (or an #N/A error if there is no match).

Value Sought Is before First Range

If you set VLOOKUP to use a "close" match (by omitting range_lookup, or setting it to TRUE), VLOOKUP will look for the greatest value in the first column of the lookup table that is less than or equal to the value being sought.  However, if the value being sought is less than every value in that first column of the lookup table, VLOOKUP will return a #N/A error.  I term this failure "value sought is before first range", and the example below illustrates how it can occur.


In this case, in the first block, VLOOKUP returns an error when it looks for zero in the lookup table: the smallest value in the lookup table's first column is 1, and zero is less than 1.

In the second block, the formulae all return the correct answer, this time because all of the values being sought have a value in the lookup table less than or equal to themselves.

To avoid this error, if you are using VLOOKUP to find a "close" match, always make sure that your lookup table is sorted properly, and that the  smallest value in your lookup table is less than or equal to the smallest value you could conceivably pass to VLOOKUP for the first argument (lookup_value).

No Match in Lookup Table

If you use FALSE for the optional fourth argument (range_lookup), VLOOKUP will look for an exact match in the lookup table.  If it finds an exact match, then it will return a value from the Nth column of the corresponding row; if not, VLOOKUP will return an #N/A error.  The example below illustrates the result when you seek an exact match for a value that is not in the lookup table.


In this case, the value 26 does not exist in the lookup table, and so VLOOKUP returns an error in G7.

A possible fix for this situation would be to omit the fourth argument, and thus revert to a "close" match, as is done in the second group.  After doing this, VLOOKUP will return "violet" in G18:

(Of course, while that construction avoids the #N/A value, it does not follow that that was in fact the "correct" result: the user may still have needed an exact match.)

To avoid this error, first be sure to determine whether or not you do in fact need an exact match--if you do not truly need an exact match, then omit range_lookup or use TRUE to allow a "close" match (and then be sure that the lookup table is sorted properly).  If you really do need to have an exact match, avoid this error by making sure that your lookup table contains every conceivable value that VLOOKUP may look for.

Data Type Mismatch

VLOOKUP is sensitive to data types, and so the data type for the lookup_value must match the data type for the values in the first column of your lookup table.  If these data types are not consistent, your VLOOKUP formula will fail, and VLOOKUP will return an #N/A error.  Consider the examples below.


In these examples, the first VLOOKUP formula in each group returns an #N/A error because of a data type mismatch:

To avoid this error, always make sure that you use consistent data types for your lookup_value and the first column of your lookup table.  Be especially careful when copying and pasting values from another application, or importing/exporting values from another application, as such values may often appear to be true numeric or date values, but actually be stored as text by Excel.  Values that appear to be numbers or dates can be coerced into numbers or dates, such as by using the VALUE or DATEVALUE functions, or by using Paste Special to multiply each cell in a range by 1.

Extraneous Spaces

Extraneous spaces--leading spaces, trailing spaces, or additional spaces in between words--either in the lookup_value or in the values in the first column of the lookup table, can also cause VLOOKUP to return errors or unexpected values.  Consider these examples below:


In each case, an extraneous leading or trailing space, either in the lookup_value or in the lookup table, causes VLOOKUP to fail to find an exact match, and thus return an #N/A error.  (In these cases, the "extraneous spaces" failure is thus a special case of the more general "no match" failure discussed previously.)

Extraneous spaces can also cause VLOOKUP to fail when using a "close" match, as extraneous spaces can:

To avoid this error, always make sure not to include leading or trailing spaces in your values, and to be consistent in hpw many spaces are used in between words in text entries.  Extraneous spaces can be removed by using the TRIM function.

Special Characters

Occasionally, your lookup_value or the values in your lookup table may contain one or more "special" characters: characters in the ANSI character set that do not typically relate to a symbol (or forced white space) in printed output.  The most commonly encountered "special characters" in this sense are the tab (ANSI 9), linefeed (ANSI 10), carriage return (ANSI 13), and   non-breaking space (ANSI 160).  The presence of any of these can cause your VLOOKUP formulae to fail, as in the examples below.


In each case, the presence of one of these special characters caused an exact match to fail.  In addition, as with extraneous spaces, special characters can also cause VLOOKUP to return an #N/A error or an unexpected result when looking for a close match.

To avoid this error, be careful not to include special characters in your lookup_value or in the lookup table.  Be especially careful with text copied and pasted from a web page, as such content frequently uses non-breaking spaces.  The CLEAN function will remove most special characters from a string, but will not remove the non-breaking space.  Thus, you may have to use a formula such as this to remove special characters from a string:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

In this case, CLEAN removes most special characters, and SUBSTITUTE replaces any non-breaking spaces with regular spaces; the TRIM function finishes the job by removing any extraneous spaces that SUBSTITUTE may have introduced.

Error Handling for VLOOKUP

In some cases, avoiding an #N/A error in your VLOOKUP formula may not be feasible, but at the same time, you may still need to "handle" the error, either by preventing its display the error in your workbook, or by substituting another value for the error.  The latter is especially important for cases in which other formulae may refer to the result of your VLOOKUP formula, as passing an error value to a dependent formula will cause that formula, and its dependents, to also produce errors.

In the first case, in which we want to "hide" an error value from view, perhaps the easiest way is through Conditional Formatting.  In this case, we would use a formula-based rule to check the cell for an #N/A value.  Thus, if the cell being checked is A2, then we would use the following formula:

=ISNA($A$2)

If, on the other hand, we needed to check every cell in the range A2:A100, then we would apply the following formula:

=ISNA(A2)

The relative range reference in that formula ensures that the Conditional Formatting rule applied to each cell refers to itself.

To "hide" the error, set the formatting such that the font color is the same as the cell's background color.

To instead substitute another value in the case of an error, we must adjust our basic VLOOKUP formula.  Consider the following VLOOKUP formula:

=VLOOKUP(A2,Sheet2!A1:Z100 ,26,FALSE)

If Excel 2003 and earlier, if we want to make that formula return an alternate value in the event the lookup_value is not found in the lookup table, we could adjust that formula to use an IF expression to test for an error condition:

=IF(ISNA(VLOOKUP(A2,Sheet2 !A1:Z100,2 6,FALSE)), "not found",VLOOKUP(A2,Sheet2!A 1:Z100,26, FALSE))

In this formula, we first test for the error condition, and if it exists we return the alternate text "not found"; if there is no error, then we get the result of the VLOOKUP expression.

In Excel 2007 and later, this is somewhat simplified by using the new function IFERROR:

=IFERROR(VLOOKUP(A2,Sheet2 !A1:Z100,2 6,FALSE)," not found")

In that construction, Excel returns the value in the first argument (in this case, a VLOOKUP expression) unless it is an error, in which case it returns the value in the second argument.

Sample File

The sample file below contains all of the illustrated examples referred to in the various sections of this article.

VLOOKUP-Failure.xls


=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=
If you liked this article and want to see more from this author,   please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=