by Is your VLOOKUP pulling wrong data or you cannot get it to work at all? This tutorial shows how you can quickly fix common VLOOKUP errors and overcome its main limitations.
In a few earlier articles, we 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 reason that many Excel specialists consider VLOOKUP to be one of the most intricate Excel functions. It has a ton of limitations, which are the source of various problems and errors.
In this article, you will find simple explanations of the main causes of VLOOKUP errors such as #N/A, #NAME and #VALUE, as well as their solutions and fixes. We will start with the 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 #N/A error in VLOOKUP
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. The lookup value is misspelt
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 your formula looks up the closest match, (range_lookup argument set to TRUE or omitted), the #N/A error can appear in two cases:
- The lookup value is smaller than the smallest value in the lookup array.
- The lookup column is not sorted in ascending order.
3. #N/A in exact match VLOOKUP
If you are searching for exact match (range_lookup argument set to FALSE), the #N/A error occurs when a value exactly equal to the lookup value is not found. For more information, see VLOOKUP exact match vs. approximate match.
4. The lookup column is not the leftmost column of the table array
One of the most significant limitations of Excel VLOOKUP is that it cannot look to its left. Consequently, a lookup column should always be the leftmost column in the table array. In practice, we often forget about this and end up with #N/A errors.
Solution: If it is not possible to restructure your data so that the lookup column is the left-most column, you can use the INDEX and MATCH functions together as an alternative to VLOOKUP. Here's a formula example: INDEX MATCH formula to look up values to left.
5. Numbers are formatted as text
Another common source #N/A errors in VLOOKUP formulas is numbers 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 show leading zeros.
Here are the most obvious indicators of numbers formatted as text:
Solution: Select all the problematic numbers, click on the error icon and choose Convert to Number from the context menu. For more information, please see How to convert text to number in Excel.
6. 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 datasets where most of the entries are below the scroll.
Solution 1: Extra spaces in the lookup value
To ensure the correct work of your VLOOKUP formula, wrap the lookup value in the TRIM function:
=VLOOKUP(TRIM(E1), A2:C10, 2, FALSE)
Solution 2: Extra spaces in the lookup column
If extra spaces occur in the lookup column, there is no easy way to avoid #N/A errors in VLOOKUP. Instead, you can use a combination of INDEX, MATCH and TRIM functions as an array formula:
=INDEX(B2:B10, MATCH(TRUE, TRIM(A$2:A$10)=TRIM(E1), 0))
Since this is an array formula, don't forget to press Ctrl + Shift + Enter to properly complete it (in Excel 365 and Excel 2021 where arrays are native, this also works as a regular formula).
Tip. A quick alternative is running the Trim Spaces tool that will eliminate excess spaces both in the lookup and main tables in seconds, making your VLOOKUP formulas error-free.
#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 three common sources of the VALUE! error.
1. Lookup value exceeds 255 characters
Please be aware that VLOOKUP cannot look up values containing more than 255 characters. If your lookup values exceed this limit, a #VALUE! error will be displayed:
Solution: Use an INDEX MATCH formula instead. In our case, this formula works perfectly:
=INDEX(B2:B7, MATCH(TRUE, INDEX(A2:A7= E1, 0), 0))
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 it. More precisely, you have to enclose the workbook's name including the extension in [square brackets] and specify the sheet's name followed by the exclamation mark. If the workbook name or sheet name, or both, contain spaces or any non-alphabetical characters, the path must be enclosed in single quotation marks.
Here's the structure of the table_array argument to Vlookup from another workbook:
'[workbook name]sheet name'!range
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 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 information, please see:
3. The col_index_num argument is less than 1
It's hard to imagine a situation when someone intentionally enter a number less than 1 to specify the column to return values from. But it may happen if this argument is returned by some other function nested in your VLOOKUP formula.
So, if the col_index_num argument is than 1, your formula will return the #VALUE! error too.
If col_index_num is greater than the number of the columns in the table array, VLOOKUP produces the #REF! error.
Solving 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 :)
The main causes of errors in Excel VLOOKUP
Apart from having a fairly complicated syntax, VLOOKUP has arguably more limitations than any other Excel function. Because of these limitations, a seemingly correct formula may often deliver results different from what you expected. Below you will find solutions for a few typical scenarios when VLOOKUP fails.
VLOOKUP is case-insensitive
The VLOOKUP function does not distinguish the letter case and teats lowercase and uppercase characters as identical.
Solution: Use VLOOKUP, XLOOKUP or INDEX MATCH in combination with the EXACT function that can match text case. You can find the detailed explanations and formula examples in this tutorial: 5 ways to do a case-sensitive Vlookup in Excel.
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 the lookup table. This happens because the syntax of the VLOOKUP function requires defining the index number of the return column. When a new column is added to/removed from the table array, obviously that index number changes.
Solution: The INDEX MATCH formula comes to the rescue again : ) With INDEX MATCH, you specify the lookup and return ranges separately, so you are free to delete or insert as many columns as you want without worrying about updating every associated formula.
Cell references change when copying the formula to other cells
The heading gives an exhaustive explanation of the problem, right?
Solution: Always use absolute references (with the $ sign) for the table_array argument, e.g. $A$2:$C$100 or $A:$C. You can quickly switch between different reference types by pressing the F4 key.
VLOOKUP returns the first found value
As you already know, Excel VLOOKUP returns the first value it finds. However, you can force it to bring the 2nd, 3rd, 4th or any other occurrence you want. There is also a way to get the last match or all found matches.
Solutions: Formula examples are available here:
Why does my VLOOKUP work for some cells but not others?
When your VLOOKUP formula returns the correct data I some cells and #N/A errors in others, there can be a few possible reasons why that happens.
1. The table array is not locked
Suppose you have this formula in row 2 (say in E2), which works nicely:
=VLOOKUP(D2, A2:B10, 2, FALSE)
When copied to row 3, the formula changes to:
=VLOOKUP(D3, A3:B11, 2, FALSE)
Because a relative reference is used for table_array, it changes based on the relative position of the row where the formula is copied, in our case from A2:B10 to A3:B11. So, if the match is in row 2, it won't be found!
Solution: When using a VLOOKUP formula for more than one cell, always lock the table array reference with the $ sign like $A$2:$B$10.
2. Text values or data types do not match
Another common reason for VLOOKUP failure is the difference between your lookup value and a similar value in the lookup column. In some cases, the difference is so subtle that it's hard to spot visually.
Solution: When VLOOKUP is returning an #N/A error while you can clearly see the lookup value in the lookup column, and apparently both are spelt exactly the same, the first thing you need to do is to determine the root cause of the problem - the formula or the source data.
To see whether the two values are the same or different, make a direct comparison in this way:
=E1=A4
Where E1 is your lookup value and A4 is an identical value in the lookup column.
If the formula returns FALSE, that means the values differ in some way, though they look absolutely alike.
In case of numeric values, the most possible reason is numbers formatted as text.
In case of text values, most likely the problem is in excess spaces. To verify this, find out the total length of the two strings using the LEN function:
=LEN(E1)
=LEN(A4)
If the resulting numbers are different (like in the screenshot below), then you've pinpointed the culprit - extra spaces:
To solve the issue, either remove extra spaces or use this INDEX MATCH TRIM formula as a workaround.
Why does my VLOOKUP pull wrong data?
There could be even more reasons why your VLOOKUP returns a wrong value:
- Incorrect search mode. If you want an exact match, be sure to set the range_lookup argument to FALSE. The default is TRUE, so if you omit this argument, VLOOKUP will assume you are looking for an approximate match and search for the closest value that is smaller than the lookup value.
- The lookup column is not sorted. For approximate match VLOOKUP (range_lookup set to TRUE) to work correctly, the first column in the table array must be sorted in ascending order, from smallest to largest.
- Duplicates in the lookup column. If the lookup column contains two or more duplicate values, VLOOKUP will return the first found match, which may not be the one you expect.
- Incorrect return column. Double-check the index number in the 3rd argument :)
VLOOKUP not working between two sheets
First off, it should be noted that the common reasons of #N/A, #VALUE, and #REF errors discussed above may cause the same problems when looking up from another sheet. If it's not the case, check out the following points:
- Make sure the external reference to another sheet or a different workbook is correct.
- When doing a Vlookup from another workbook which is closed at the moment, verify that your formula contains the full path to the closed workbook.
- If VLOOKUP cannot pick a table array in another worksheet (i.e. when you highlight a range in the lookup sheet, nothing appears in the table_array argument in the formula or in the corresponding box of the formula wizard), then most likely the two sheets are open in separate instances of Excel and cannot communicate with each other. For more info, please see How to determine which Excel files are in which instance. To fix this, simply close all Excel windows, and then reopen the sheets/workbooks in the same instance (the default behavior).
How to Vlookup without errors in Excel
If you do not want to intimidate your users with standard Excel error notations, you can display your own user-friendly text instead or return a blank cell if nothing is found. This can be done by using VLOOKUP with IFERROR or IFNA function.
Catch all errors
In Excel 2007 and later, you can use the IFERROR function to check a VLOOKUP formula for errors and return your own text (or an empty string) if any error is detected.
For example:
=IFERROR(VLOOKUP(E1, A2:B10, 2, FALSE), "Oops, something went wrong")
In Excel 2003 and earlier, you can use the IF ISERROR formula for the same purpose:
=IF(ISERROR(VLOOKUP(E1, A2:B10, 2, FALSE)), "Oops, something went wrong", VLOOKUP(E1, A2:B10, 2, FALSE))
For more details, please see Using IFERROR with VLOOKUP in Excel.
Handle #N/A errors
To trap only #N/A errors ignoring all other error types, use the IFNA function (in Excel 2013 and higher) or IF ISNA formula (in all versions).
For example:
=IFNA(VLOOKUP(E1, A2:B10, 2, FALSE), "Oops, no match is found. Please try again!")
=IF(ISNA(VLOOKUP(E1, A2:B10, 2, FALSE)), "Oops, no match is found. Please try again!", VLOOKUP(E1, A2:B10, 2, FALSE))
That's all for today. Hopefully, this tutorial will help you get rid of VLOOKUP errors and have your formulas working in the way you want.
How to VLOOKUP in Excel - video tutorial
47 comments
I am using the vlookup formula, and i can not password protect the sheets as my whole department needs to access them. I use the vlookup successfully and Crtl+C and ctrl+v isn’t pasting the values from the other sheet without the formula and giving a locked formula error so all of the changes are affecting the original sheet as well.
Paste with CTRL+SHIFT+V (Paste As Special.. 'Value').
Hi everyone,
If you are reading this post then you have an issue with VLookup. I spent hours trying to work out the problem.
For me the problem when copying down / pasting the VLookup formula as that my calculations were set to manual.
Go into excel and in the search bar type "Calculation" and see if Automatic is selected or if for some reason Manual is selected.
Change to Automatic and hopefully your VLoopup past will now work.
omg thank you.
Hi Sir
Would you please help to sort out my problem? I have a work sheet where VLOOKUP working properly but when i add a cell value that contain sheet name using formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). No issue at all, adding sheet name manually in that cell. but adding with formula i got error.
Hello!
The CELL function returns a text string, not a link. To create a link from this text, use the INDIRECT function. I recommend reading this guide: Excel INDIRECT function - basic uses and formula examples.
I hope this will help, otherwise don't hesitate to ask.
I have copied a Vlookup down a number of rows in sections of a worksheet but occasionally the relative cell reference being checked against the other data source, for e.g. D3 to fill data in B3 and C3 changes to D5 although is in a protected cell and always seems to jump by 2 rows? Would you have any ideas please? Many thanks.
put coma after the column and wrire FALSE and close the bracket
for example $C$8:$l$120,2,FALSE
Hi!
If you use a relative cell reference and copy the formula down the column, only the row number may change. The column cannot change. Therefore, I cannot understand your problem.
Write what formula you use and what actions you perform.
Hi,
Thanks for your help on this and sorry for not being clearer.
I have formula below in AM68:
=IFERROR(VLOOKUP(AP68,NEAlloc!$A$4:$U$300,5,FALSE),"")
and this one in AN68:
=IFERROR(VLOOKUP(AP68,NEAlloc!$A$4:$U$300,7,FALSE),"")
I have then copied this down a number of rows underneath and generally works okay but sometimes the row number changes even though it’s in a locked protected cell. So for example AP68 might become AP70 in both AM68 and AN68. I have a number of blocks on same worksheet set up in this way.
I hope you can see the situation now.
I look forward to hearing back from you.
Many thanks
Julian
Hi!
When you copy a formula from AM68 to AM69, the relative reference in the formula should change. You will see in AM69 -
=IFERROR(VLOOKUP(AP69,NEAlloc!$A$4:$U$300,5,FALSE),””)
This relative reference will also change if you insert or delete rows above the formula.
how to use Vlookup with If AND or function
Hello!
Please have a look at this article — IF VLOOKUP in Excel: Vlookup formula with If condition.
So my current issue is number with decimals. I got several sets of data with 123456.xyz. XYZ changes on each of them. I tried to vlookup with first 6 numbers, it wont detect if there is decimals involved. Any work around for that?
Hello!
The VLOOKUP function searches for an exact match. If you are not using decimal numbers for your search, use the ROUND function to round your results.
Thanks for the reply, tho in my case number after decimals important for me so rounding doesn't help. I was basically looking a way to sort the numbers based on values before decimals as they are fixed and values after decimals change. I guess VLOOKUP function can't help with that. Back to the drawing board!
Hello!
You can round to 2 or 3 decimal places that are important to you.
ANOTHER POSSIBLE REASON VLOOKUP GET #N/A
If the lookup column has multiples of the same value it confuses lookup and will cause the #/NA error. In other words, the lookup value must have a unique presence in the lookup column.
Hi Jason,
In case of multiple matches, VLOOKUP returns the first found match. This may be confusing in many situations, so I agree that you'd better have unique values in the lookup column.
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.
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.
Hey All,
I have been looking into a few options to try and rectify a lookup I am doing which pulls from another tab. Currently I can replace the blank cells which are showing as 0 with the following formula, however I now also need to eradicate n/as that show when the lookup returns no match against the cell targeted. Please see below:
=IF(VLOOKUP([extract]],Table13[[Num]:[extract1 ]],13,FALSE)="","",VLOOKUP([extract],Table13[[Num]:[extract1]],13,FALSE))
The above works fine, however returns n/a's. I have tried to use "iferror" without success. I need to exclude both 0's and n/a's
Thanks.
In Excel 2013 you can go do the following to remove the zeros:
File>Options>Advanced>Display options for this worksheet: (use the drop down arrow to select the correct worksheet if more than one in your file).
Make sure that the following option is deselected: Show a zero in cells that have a zero value
Good luck with the #N/A issue.
You can also try the following for the "N/A issue:
File>Options>Formulas>Error checking rules:
Make sure that the following option is deselected: Formulas referring to emtpy cells
In certain parts of my worksheet , when i enter the vlookup function it doesnt do anything it remians as text format
Hi Mahir,
Most likely it's because the format of those cells is set to "Text". Changing the cell format to "General" should fix it. If it doesn't, check out the following solutions:
Why is Excel showing a formula, not result?
Hello, Mahir,
Please make sure you enter the equal sign before adding the formula.
Hi, svetlana cheusheva
i have a query related to excel please help me
how to highlight past dates?
Hope you help me!
I think Formula is =a2<today() it is work.
I keep having my vlookup column doing this. Is there something I have to do to correct this?
=VLOOKUP(A4,October!A:A,1,0)
=VLOOKUP(A4,October!A:A,1,0)
=VLOOKUP(A4,October!A:A,1,0)
=VLOOKUP(A4,October!A:A,1,0)
=VLOOKUP(A4,October!A:A,1,0)
=VLOOKUP(A4,October!A:A,1,0)
=VLOOKUP(A4,October!A:A,1,0)
=VLOOKUP(A4,October!A:A,1,0)
Hello, Dee,
You can add a check for the found value:
=IF(ISERROR(VLOOKUP(A4,October!A:A,1,0)), "",VLOOKUP(A4,October!A:A,1,0))
WHATS with wrong (( ? )))
I have a spreadsheet that we update daily on one tab and we use a vlookup to update information on the main tab. The problem is that there are several client names that won't update automatically after we paste the new data. We have to clear their name each day and retype it for the vlookup to work. I have ran trim and that doesn't work. Plus my colleague cannot even update the name. The vlookup won't work at all if she updates from her excel. The names are in alphabetical order. The range is covered. THe names aren't all at the end they really have nothing in common that I can see. It seems completely random. If I retype their names the vlookup does not return the #NA but if my colleague retypes their names her Excel does not update the data. I have gone crazy trying to figure this out. Help!
We detfniiely need more smart people like you around.
The problem I frequenly run in to is the data that is being displayed by the Vlook up is both correct and incorrect. It pulls data from the wrong cell. Im pritty sure I set everything up correctly but why would it present incorrect data why not just N/A or error? In addition some of the data is represented properly. Seems sporadic on one sheet driving me nuts.
Hello,
Most likely the issue is in your data or in the way they are presented: the cells format, excess spaces, merge cells, etc.
1- Can i use vlookup formula to search for data in an excel sheet that has blank rows or columns?
2- can i build my vlookup formula on the right of the data that I need to search it?
thanks,
Tony
Hi Tony,
1) yes, the VLOOKUP formula can be used to search through blank rows/columns.
2) yes, it's up to You where You place your formula.
It's just not working. I used the wizard to build the formula =VLOOKUP('Tracking In Progess'!A:A,A:A,1,'Tracking In Progess'!A:C) and it always returns #VALUE!-- I tried swapping the lookup_value and table_array and it always returns #VALUE! I've tried doing this with five different workbooks with different data. It never works. I've tried it in 2010 and 2013 and the result is always the same. I need this function -- but I hate it!
It works now that I followed Derek's comment.
Thank you Svetlana
you are really sweet girl. what you are presenting here of useful material reflects your sweetness, kind heart and sole.
Thank you again love you and keep on.
Cheers
Jafar
Saudi Arabia
Thank you very much for your nice words, Jafar! You are too kind.
We had a problem when we upgraded to 2013.
When we sorted a table with a column containing the VLOOKUP formula
=VLOOKUP(Analysis!D3,DEPT2,2,FALSE)
The formula kept the reference to original cell that we were referring to
So, if the above formula was located on row 3, and the sort moved it to row 41, it would still reference D3, rather than D41
The table was located in the worksheet named "Analysis", so that worksheet reference was superfluous, and when we removed Analysis! from the formula, it adapted correctly to the new row following the sorting of the table.
Hello Derek,
Thank you very much for sharing this information. I've tested this behavior and got the same results.
you're so pretty