Excel VLOOKUP not working - solutions for N/A, NAME and VALUE errors

The tutorial explains how you can quickly cope with VLOOKUP not working problems in Excel 2016, 2013, 2010, 2007 and 2003, troubleshoot and fix common errors and overcome VLOOKUP's limitations.

In the last few articles, we have explored different aspects of the Excel VLOOKUP function. If you have been following us closely, by now you should be an expert in this area : )

However, it's not without a reason that many Excel specialists consider VLOOKUP to be one of the most intricate Excel functions. It has a ton of limitations and specificities, which are the source of various problems and errors.

Excel VLOOKUP not working: #N/A, #VALUE, #NAME errors In this article, you will find simple explanations of VLOOKUP's #N/A, #NAME and #VALUE error messages as well as solutions and fixes. We will start with the most frequent cases and most obvious reasons why vlookup is not working, so it might be a good idea to check out the below troubleshooting steps in order.

Fixing VLOOKUP N/A error in Excel

In Vlookup formulas, the #N/A error message (meaning "not available") is displayed when Excel cannot find a lookup value. There can be several reasons why that may happen.

1. A typo or misprint in the lookup value

It's always a good idea to check the most obvious thing first : ) Misprints frequently occur when you are working with really large data sets consisting of thousands of rows, or when a lookup value is typed directly in the formula.

2. #N/A in approximate match VLOOKUP

If you are using a formula with approximate match (range_lookup argument set to TRUE or omitted), your Vlookup formula might return the #N/A error in two cases:

  • If the lookup value is smaller than the smallest value in the lookup array.
  • If the lookup column is not sorted in ascending order.

3. #N/A in exact match VLOOKUP

If you are searching with exact match (range_lookup argument set to FALSE) and the exact value is not found, the #N/A error is also returned. See more details on how to properly use exact and approximate match VLOOKUP formulas.

4. The lookup column is not the leftmost column of the table array

As you probably know, one of the most significant limitations of Excel VLOOKUP is that it cannot look to its left, consequently your lookup column should always be the left-most column in the table array. In practice, we often forget about this and end up with VLOOKUP not working because of the N/A error.
The lookup column should be the leftmost column of the table array, otherwise VLOOKUP displays the N/A error.

Solution: If it is not possible to restructure your data so that the lookup column is the left-most column, you can use a combination of Excel's INDEX and MATCH functions, as a more versatile alternative to VLOOKUP. You will find the detailed info and a formula example in this tutorial - INDEX / MATCH formula to lookup values to left.

5. Numbers are formatted as text

Another source N/A errors in VLOOKUP formulas is numbers being formatted as text, either in the main or lookup table.

This usually occurs when you import data from some external database or if you've typed an apostrophe before a number to indicate a leading zero.

The most obvious indications of numbers being formatted as text are shown in the screenshot below.
The indications of numbers being formatted as text in Excel

The numbers can also be stored in the General format. In this case, there's only one noticeable sign - numbers get aligned to the left side of a cell, while numbers stored as numbers are aligned to the right by default.

Solution: If this is just a single number, simply click on the error icon and choose "Convert To Number" from the context menu.
Converting numbers formatted as text to the normal numbers format.

If multiple numbers are affected, select them all, right-click the selection, then choose Format Cells > Number tab > Number and click OK.

6. Excess leading or trailing spaces

This is the least obvious cause of the Vlookup N/A error because a human eye can hardly spot those extra spaces, especially when working with big tables where most of the entries are below the scroll.

Solution 1: Extra spaces are in the main table (with VLOOKUP formulas)

If excess spaces occur in your main table, you can ensure the correct work of your Vlookup formulas by wrapping the lookup_value argument with the TRIM function:

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
If extra spaces occur in the main table, wrapping the lookup value with the TRIM function.

Solution 2: Extra spaces are in the lookup table (lookup column)

If extra spaces occur in the lookup column, there is no easy way to avoid VLOOKUP #N/A errors. Instead of VLOOKUP, you can use an array formula with a combination of INDEX / MATCH and TRIM functions:

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))

Since this is an array formula, don't forget to press Ctrl + Shift + Enter rather than a usual Enter keystroke to properly complete it:
If extra spaces occur in the lookup table, use INDEX / MATCH / TRIM instead of VLOOKUP.

For more information about using INDEX / MATCH in Excel, please check out this tutorial: INDEX & MATCH in Excel - a better alternative to VLOOKUP.

Tip. A quick alternative to complex INDEX / MATCH formulas is running the Trim Spaces for Excel add-in that will eliminate excess spaces both in the lookup and main tables in seconds, making your VLOOKUP formulas error-free. This is a free tool and you can find the download link on the above page.

#VALUE error in VLOOKUP formulas

In general, Microsoft Excel displays the #VALUE! error if a value used in the formula is of a wrong data type. In respect to VLOOKUP, there are two common sources of the VALUE! error.

1. Lookup value exceeds 255 characters

Please be aware that the VLOOKUP function cannot look up values containing 256 characters or more. If your lookup values exceed this limit, you will end up having the VALUE error:

The VALUE error in VLOOKUP when a lookup value exceeds 255 characters.

Solution: Use an analogous INDEX /MATCH formula instead. In the above example, the following INDEX / MATCH function works perfectly:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7= F$2,0),0))

If a look up value contains 256 characters or more, use INDEX / MATCH instead of VLOOKUP

You can learn more about using INDEX / MATCH in Excel in this tutorial.

2. Full path to the lookup workbook is not supplied

If you are pulling data from another workbook, you have to include the full path to that file. More precisely, you have to enclose the workbook's name (including the extension) in square brackets [], and then specify the sheet's name followed by the exclamation mark. Also, you should have apostrophes around all this in case either a workbook or spreadsheet name contains spaces.

Here's the structure of the complete formula to do a vlookup from another workbook:

=VLOOKUP(lookup_value, '[workbook name]sheet name'!table_array, col_index_num, FALSE)

A real formula might look similar to this:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)

The above formula will search for the value of cell A2 in column B of Sheet1 in the "New Prices" workbook, and return a matching value from column D.

If any element of the path is missing, your VLOOKUP formula won't work and return the #VALUE error (unless the lookup workbook is currently open).

For more info about VLOOKUP formulas referencing another Excel file, please check out this tutorial: How to do vlookup from a different workbook.

3. The col_index_num argument is less than 1

It's hard to imagine a situation when someone would want to enter a number less than "1" to specify the column to return values from. Though, it may happen if this argument is returned by some other Excel function nested in your Vlookup formula.

So, if the col_index_num argument happens to be less than 1, your Vlookup formula will return the #VALUE! error too.

If the col_index_num argument is greater than the number of the columns in the specified table array, Vlookup formulas return the #REF! error.

VLOOKUP #NAME error

This is the easiest case - the NAME error appears if you've accidentally misspelled the function's name.

The solution is obvious - check the spelling : )

Excel VLOOKUP not working (limitations, issues and solutions)

Apart from having a fairly complicated syntax, VLOOKUP has arguably more limitations than any other Excel function. Because of these limitations, seemingly correct Vlookup formulas might often deliver results different from what you expect. Below you will find solutions for a few common scenarios when VLOOKUP fails.

1. VLOOKUP is case-insensitive

The VLOOKUP function does not distinguish case and teats lowercase and UPPERCASE characters as identical. So, if your table includes several similar entries that differ in the uppercase or lowercase chars only, the Vlookup formula will return the first found value regardless of the case.

Solution: Use another Excel function that can do a vertical lookup (LOOKUP, SUMPRODUCT, INDEX / MATCH) in combination with the EXACT function that can match case. You can find the detailed explanations and formula examples in this tutorial - 4 ways to do a case-sensitive vlookup in Excel.

2. VLOOKUP returns the first found value

As you already know, Excel VLOOKUP returns the first value it finds in the return column that matches the lookup value. However, you can force it to bring the 2nd, 3rd, 4th or any other occurrence you want. If you need to get all duplicate occurrences, you will have to use a combination of the INDEX, SMALL and ROW functions.

Solutions: Formula examples are available for download here:

3. A new column was inserted or removed from the table

Regrettably, VLOOKUP formulas stop working every time when a new column is deleted from or added to a lookup table. This happens because the syntax of the VLOOKUP function requires that you supply the entire table array as well as a certain number indicating which column you want to return the data from. Naturally, both the table array and the return column's number change when you remove an existing column or insert a new one.

Solution: INDEX / MATCH comes to the rescue again : ) In INDEX & MATCH formulas, you specify the lookup and return columns separately, and as a result you can delete or insert as many columns as you want without worrying about updating every associated vlookup formula.

4. Cell references changed when copying the formula to other cells

The heading gives an exhaustive explanation of the problem, right?

Solution: Always use absolute cell references (with the $ sign) in table_array, e.g. $A$2:$C$100 or $A:$C. In the formula bar, you can quickly switch between different reference types by pressing F4.

VLOOKUP with IFERROR / ISERROR

If you do not want to intimidate your users with all those N/A, VALUE or NAME error messages, you can return a blank cell instead, or display your own message. You can do this by wrapping your VLOOKUP formula in the IFERROR function in Excel 2016, 2013, 2010 and 2007 or with IF / ISERROR in earlier Excel versions.

Using VLOOKUP with IFERROR

The syntax of the IFERROR function is simple and self-explanatory : )

IFERROR(value,value_if_error)

Meaning, you enter the value to check for an error in the 1st argument, and in the 2nd argument you specify what to return if an error occurs.

For example, the following IFERROR / VLOOKUP formula returns a blank cell when the lookup value is not found:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
The IFERROR / VLOOKUP formula returns a blank cell instead of the error message.

If you'd rather display your own message instead of a standard Vlookup error, type it between the quotation marks, like this:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Oops, no match is found. Please try again!")
Display your message instead of VLOOKUP error.

Using VLOOKUP with ISERROR

Since the IFERROR function was introduced in Excel 2007 only, in lower Excel versions you will have to use the combination of IF and ISERROR functions in this way:

=IF(ISERROR(VLOOKUP formula), "Your message if any", VLOOKUP formula)

For example, here's the IF / ISERROR / VLOOKUP formula analogous to the IFERROR / VLOOKUP formula above:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)), "", VLOOKUP($F$2,$B$2:$C$10,2,FALSE))

That's all for today. Hopefully, this short tutorial will help you cope with all possible VLOOKUP errors and get your formulas to work in the way you want.

How to VLOOKUP in Excel - video tutorial for beginners

You may also be interested in:

124 Responses to "Excel VLOOKUP not working - solutions for N/A, NAME and VALUE errors"

  1. Prakash KC says:

    =IF(K9="one",IFERROR(VLOOKUP(N9,[One.xlsx]Ledger!$A$12:$BI$29,3,FALSE),"No Data"))&IF(K9="two",IFERROR(VLOOKUP(N9,[Two.xlsx]Ledger!$A$12:$BI$29,3,FALSE),"No Data"))&IF(K9="three",IFERROR(VLOOKUP(N9,[three.xlsx]Ledger!$A$12:$BI$29,3,FALSE),"No Data"))

    Result FALSEMANISHA BISTAFALSE

    how to remove FALSE

  2. Badri says:

    Hi,

    I have problem with vlookup, if i perform the vlookup in the same file with different sheets, the formula is giving me partial results. However if i vlookup the same data from a different excel file, its giving me the complete results.

    what would be the reason, Is there something i can do with the formula to work perfectly within sheets?

  3. charles says:

    I did a vlookup and the output showed this number (15359E+11) instead of the actual digits. I have not had this kind of error before. Please advise.

  4. Frank says:

    The iferror function work greatly, the if(iserror way may still produce #VALUE! cells.

  5. JO CHAWANDIT says:

    THANK YOU. THIS IS QUITE USEFUL.

  6. Irhan says:

    Very Useful - keep up the good work ( share the knowledge..sharing is caring )

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard