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

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.

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.

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.

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.

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.

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.

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

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

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

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

**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:

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

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.

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:

**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))`

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

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.

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.

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

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.

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.

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 2^{nd}, 3^{rd}, 4^{th} 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:

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.

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.

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.

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 1^{st} argument, and in the 2^{nd} 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),"")`

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!")`

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.

Excel formulas
CSV
Excel functions
Print
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Updates
Conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

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

=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

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?

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.

Just convert it to NUMBER

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

THANK YOU. THIS IS QUITE USEFUL.

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

=VLOOKUP(IF(BC10>=130%,"130% & Above",IF(BC10<70%,"0.0 - 69%",ROUNDDOWN(BC10,2))),INDIRECT(BL10),2,0)

so the result should show rating

Sales Target RM25mil & Above

Rating Rating Range Ach % Rating Ach %

Outstanding 4.75-5.00 126% - Above 5.00 130% & Above

4.94 129%

4.88 128%

4.81 127%

4.75 126%

4.50-4.74 121% - 125% 4.74 125%

4.68 124%

4.62 123%

4.56 122%

4.50 121%

I have data in two different excel files. I tried to input VLOOKUP manually.

After inputting =Vlookup(A2, I tried to jump to other file and received error for formula correction.

Can you please help me to resolve this issue?

Trying to do a vlookup on a table that is 1000 rows and 26 columns. If I try to get the value in the 13th column, it works fine. However, if I try for any column greater than that I get a #REF! error. All columns have valid data. Formula is =VLOOKUP($a1,TableSheet!$A:$Z,15,FALSE). Any ideas?

If you want to get work done, and run into excel misbehaving, go find an old copy of Lotus 1-2-3, install it, copy your data in and do your lookups there. The Lotus vlookup function isn't nearly as stupid or sensitive as excel, and doesn't require the lookup table data to be sorted just so to work properly.

excellent ,thank you

Another possible issue: special characters in lookup value. E.g. tilde ~. Solution would be to duplicate this character: VLOOKUP(SUBSTITUTE(C1,"~","~~"),A:B,2,false)

i am using vlookup from another sheet, i have entered 3 entries that is ok but when i am doing next entry it pulls value from wrong row. please help me i have my file if you ask me i can mail you my file.

Hi, i use vlookup to match in the completion status from another file. but then some of my cell does not capture the formula and turn out the result is wrong. would anyone please help me out in this matter.

When using vlookup, the formula only displays moot the value. No error messages. Any ideas?

Thanks for this outstanding reference, much appreciated! Also, good to know I'm not the only one that struggles with this in Excel.

Thank you for this, very helpful!

I am using vlookup to copy values from one table to another. Many of the values have been copied, but not all. Those that have not been copied are showing #NV. I have checked for all the possible reasons for the error, corrected some, but the problem persists and has not changed at all. I even tried INDEX/MATCH with no avail. Any Idea what could be the cause?

When using vlookup, I can get the correct results for most of the table. When I get down to the last 4 lines of the table, it gives me results from previous lines when i change the lookup data. I have resorted the table multiple times but always get the same incorrect response.

This is very helpfull.

VLOOKUP() will fail if, "the lookup column is not sorted in the ascending order." Well, that's just stupid.

hello,

can you help me_ please. This function =IFERROR(SVYHLEDAT(A3;'M (3)'!A3:I9988;3;1);"Not")

do not describe. Why?

Thanks

Hello Petr!

I’m sorry but your task is not entirely clear to me. What is "SVYHLEDAT"? Could you please describe it in more detail? Thank you!

i am having a lookup related problem when i am applying lookup function to a cell which does not contain the actual data but there is formula applied to get the value from other two cell so now lookup is unable to look for that cell where formula has been filled but when i am typing actual value in that cell lookup function is working correctly but when there is a formula which is showing correct value lookup function is unable to work .

For example lookup can look for a value 3 and search it in an array but when there is two column containing 1 in first column and 2 in second column and now in third column i am using formula to add them simply so i sum them and now the value i am getting is 3 in third column but when i am applying lookup function to it. It is showing #N/A as usual but when instead of formula i am directly typing the value 3 in third column the lookup formula works perfectly so any solution on this will be helpful.

Hello Rajat!

I need more information on your problem to help you.

Most importantly, what function are you using for a search? Is it VLOOKUP or anything else? Please give me the formula which is performing a search.

What data are in the cells which that formula is referring to?

Please describe your search in more detail, and I will be able to help you.

Hi

How do I get the 0 to return as text in my formula, this leads on to pivot table and if it can find the value it presents as text but if it cant it presents as value so I get two lines in my pivot? =IFERROR(VLOOKUP(A2,'[Works in RAMM by month 19-20.xlsx]Sept to June 20'!$A$1:$L$276,12,FALSE),"0 ")

thanks Mo

Hello Maureen!

If I understand your task correctly, maybe the following formula should work for you:

=IFERROR(VLOOKUP(A2,'[Works in RAMM by month 19-20.xlsx]Sept to June 20'!$A$1:$L$276,12,FALSE),"0" & " ")

Hope you’ll find this information helpful.

The numbers formatted as text gets me every time

Hello Pat!

Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

That has really fixed my problem after long time searching, Thank you !!

Hi ,

While using Vlookup ,

Lookup value is a formula based reference created using the formula

"=TRIM(LEFT(A4,5)&MID(A4,10,3)&RIGHT(A4,4))" . A4 cell value is = 10211-40-000-0000.

after allpying the above formula i get value as "102110000000" in B4.

Now if i put vlookup or index match taking "B4 cell" as lookupvalue i m getting error as "#N/A" . However if paste special the same cell with values the formula works fine .

Can you please help me on this sir ?

Thanks in Advance

Regards

Praveen L

My formula was VLOOKUP(C2,Sheet2!A2:B45,2,FALSE). The expectation was that the output should be the value from column B on Sheet2 if there is a match. However, it never returned a value. Any ideas?

A similar VLOOKUP worked on the same sheet with no issues.

Hello!

Your VLOOKUP formula has no errors. I don’t know what values you are looking for, but I suppose it is text. Perhaps there are extra spaces, non-printable characters in the text. I recommend checking for extra spaces and non-printing characters, as described in this article.

I have a simple VLOOKUP table with unordered data. I have set up the formula properly (set to exact match, to lookup value in left col and return value in the right). Format is general for both the array and for the col of values being looked up (I don't have, for example, numbers with the apostrophe in front, indicating that they are text. The formula works for all values, except as follows:

ANY VALUE THAT CONTAINS PARENTHESES RETURNS AN #N/A, EVEN THOUGH THE IDENTICAL VALUE IS IN THE LEFT COL OF THE ARRAY. I have checked character counts, I have used EXACT in a sample case to confirm that the values in the array and in the col of values that I am looking up are identical—there are no extra spaces. I have deleted the parens and confirmed that I get a value, not an #N/A. I have replaced the parens with slashes—the formula works. I HAVE REPLACED THE PARENS WITH BRACKETS—THE FORMULA RETURNS AN #N/A.

Is this a known bug?

Hello!

I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you. Thank you.

Thanks, Alexander! It's real simple—I am trying to confirm that a value in one col of my doc appears verbatim in another. Here is the set up:

Col A

value 1, 2, 3, etc. [different value in each row—the values are not ordered]

Col B

OK [I use the value "OK" in each row]

Col C

value 1, 2, 3, etc. [different value in each row—there cannot be a value in C that does not appear in A]

Col D

=VLOOKUP(C4;$A$1:$B$75;2;0) [this is the formula in row 4 of the doc—the first cell reference, Cx, always matches the given row]

I want to this formula in D to give me an #N/A if the value in C is not found in A, but an "OK" if the value in C appears in A. Very simple.

However, what I discovered is that, as soon as there are parentheses in the value, it doesn't matter if the value in C appears in A exactly as it appears in C—I get an #N/A. When I delete the parens in both A and C, I get my OK instead of the #N/A.

I am familiar with the problems created for EXACT, vLOOKUP, and other formulas by trailing space, by numbers converted to text, and so on. I am pretty confident that my problem is not due to these issues. Instead, I think the appearance of parentheses or brackets in the text causes vLOOKUP to fail. I was hoping folk could confirm—and perhaps supply a workaround. (Yes, I can replace the parens with another character before using vLOOKUP. Might there be something else to do in the formula?)

Thanks for any light you can shed!

Hello!

The problem with parentheses in the VLOOKUP function is unknown to me. This did not cause problems for me. Maybe after the bracket there is still a space or some non-printable character? Try using the TRIM or CLEAR function:

= VLOOKUP(CLEAR(TRIM(C4));$A$1:$B$75;2;0)

I hope this will help, otherwise please do not hesitate to contact me anytime.

The above guide still not solve my problem. I still

Hitting NA# in the VLOOKUP after I done edit or amend the cell format. Can I send u the file ? And you help to point out the issue ?

Hello Victoria!

I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you. Thank you.

Thank you so much. Really appreciate it.

Hi. Really appreciate the write up. I am using excel for Mac. I used vlookup to match serial numbers. For the serial numbers, I have to use the tick mark ' to keep leading 0s. Vlookup is not matching not matter what I have tried. The odd thing is that if I double click in the first cell in the array table, the tick mark is removed and the match occurs. I am trying to avoid double clicking on thousands of cells to make vlookup work. Any ideas? Kind of urgent for a data conversion. THANKS!

yeah me experience this weird error too. please any tips ASAP.

one cell is having more than 100 values. so vlookup failed and getting NA but if use find it finding the value.

Hello!

Clarify the question. One cell can only have 1 value

I am having a format issue with vlookup.

If I copy from the vlookup data sheet like an id number it will work but if I type the id number it will return #NA.

- both data and input sheet have the column set as numbers

-tried copying and pasting the formatting from data to input sheet

Any help is appreciated :)

Hello!

Unfortunately, without seeing your data it hard to give you advice.

If your numbers are derived from calculations, I recommend using rounding. Your numbers may differ in decimal place, which is not shown on the screen.