Top 3 Reasons Why My VLOOKUP Isn’t Working

by Mark Wong on February 21, 2010 · 250 comments

in Excel

1.  You Have Your Numbers Formatted as Text

2.  You Have a Trailing Space at the End of Your Values

3.  You Forgot to Reference Lock Your Arrays

During my days as the spreadsheet guy (oh wait, I still am the spreadsheet guy) I’d often get pinged by other analysts about why their VLOOKUP formulas were not working.  VLOOKUP is the most popular of all the available lookup formulas in Excel. What’s interesting is that, of the myriad of different requests I would get, the cause and the fix for each of the problems would fall into just a handful of categories.

So are you currently looking at a VLOOKUP that displays the dreaded “#N/A” result?  Before you go bug the Excel genius at your company, read below for the top three reasons why your VLOOKUP isn’t working.

VLOOKUP is an essential tool for anyone who uses spreadsheets on a regular basis. Please keep in mind that all of these reasons apply to INDEX MATCH as well, but I will use VLOOKUP syntax in the examples because it is more common.

Click here for a detailed explanation of why INDEX MATCH is better than VLOOKUP

Excel Bestsellers

1.  You Have Your Numbers Formatted as Text

None of your VLOOKUPs are working, so you click on the lookup reference of your data set. In the formula bar you see an apostrophe before your intended number entry.

Solution: Multiply all of your lookup values by 1.

This usually occurs when someone is trying to show a leading zero in front a number.  (A better way to do that is with custom formatting)  This can also happen from standard outputs of common source systems, such as Great Plains.

Multiplying a value by 1 is the easiest way to convert a value to a number.  Notice how your values are now aligned to the right, indicating that it is actually a number.

It is possible to do a VLOOKUP of the TEXT of your lookup values, but this method doesn’t truly solve the problem. Additionally, if your error comes from a data set that has both text and numbers, all your number lookups will then become erroneous. Using the multiply by 1 technique is the best way to address this issue.

2.  You Have a Trailing Space at the End of Your Values

In this example, we have a VLOOKUP formula that, from a surface view, should work as planned. But why are none of the values returning results? The answer is invisible to the naked eye. To actually see your problem, you need to highlight your lookup values and click your cursor past the very end of the entry. Yes, there’s an extra space at the end of the input.

Typically this error occurs in the lookup reference of your larger data set. It happens when people import their data from different source systems, or through flawed programming logic that neglects to remove the space in the first place. Whatever the case, the solution to this problem is easy.

Solution: Use Data – Text to Columns on your data set. Check the box to delimit new columns with “spaces” and to treat consecutive delimiters as one, just in case there is more than one space.  Assuming your data entries are each only one word, you won’t run the risk of deleting any data.

3.  You Forgot to Reference Lock Your Arrays

People rarely use the VLOOKUP formula to lookup just one value. If you’re adding an additional field to your data set or looking up based on a set of values, you’re going to need to drag your formula to copy it downward.  So you double click the lower right hand corner of the cell to populate the formula down, and lo and behold, only half the formulas are working.

Solution: Use the F4 Key to reference lock your arrays before dragging your formula down.

If you look at the example below, the lookup values you are trying to match are no longer in the lookup array and has actually shifted down.  This is because you dragged the formula down without highlighting your array references and hitting F4. While the solution to this problem is relatively simple, it’s still amazes me how many times people encounter this problem. Even if you don’t intend to drag your formulas down, it’s good practice to always reference lock your arrays when writing a VLOOKUP formula.

{ 45 comments… read them below or add one }

krishnaprasadNo Gravatar August 20, 2014 at 4:24 pm


I have made one vlookup file in excel 2013. my all vlookup s are returning #N/A . i have checked with my data and all column are checked with text to column even then it is not working.

can you help me on this regards


MengyaNo Gravatar August 26, 2014 at 10:31 pm

You are amazing!!!

JenniferNo Gravatar August 29, 2014 at 7:16 pm

Hello – I’m pretty handy with excel and vlookup and I’m running into an issue where I’m looking up a column and dragging it down and it’s giving me the same number all the way down. And the numbers should be different. I’ve never ran into the problem before. I’ve checked each formula and it is set to pull from G2, G3, G4, etc. UGH! It’s happening with all the vlookups no matter what the number format is. Oh! And… I just tried to make sure another column all had 3 digits by using the RIGHT formula and it provided the same number all the way down the column. What is going on? This are simple formulas that I’m trying to do on this spreadsheet (that multiple people use btw)

OnabrehahsNo Gravatar September 5, 2014 at 3:24 pm

Great! It did work for me. Mine had numbers stored as text but changing the format to number simply resolved the vlookup issue for me.
Thanks so much!

IanNo Gravatar October 10, 2014 at 10:02 pm

You da man thanks!

AggyNo Gravatar October 22, 2014 at 6:58 am

Hi My VLookup isnt working properly. i have using it with names and not numbers. its returning so correct and some wrong answers. how can i make it correct?

AngelaNo Gravatar October 27, 2014 at 2:14 pm

Martina–I have been going crazy over the same issue for months–your solution worked for me! I guess the vlookup table has to be in chronological order for it to work. Crazy, but sorting it worked for me, too. Thank you!!! 😀

EshwaryaNo Gravatar October 29, 2014 at 3:03 pm

Hi Guys,

My problem was not stated in Mark’s list but the solution is given 😉

Well, i was trying to apply vlookup however whenever i tried to copy the formula in other fileds of the column, the vlookup result was the same as above.
Like if the 1st vlookup value is say “Prasad”, all the other results were same.

I tried the 2nd solution form listed solutions and now it worked. Now the correct lookup values are been reflected.

Miro Z.No Gravatar January 22, 2015 at 11:59 am

Thanks for your help! F4 was the clue.

LucyNo Gravatar February 4, 2015 at 10:52 pm

My issue was hidden columns in between the table array.

MarriottNo Gravatar February 10, 2015 at 4:33 am

Recently we have upgraded from Excel 2007 to Excel 2010 and I encountered a bug with the vlookup formula.

In Sheet 2 I have table of seven columns of numeric data (from columns A-G, all general format numerical values)
In Sheet 1 I have cover sheet where I enter different values from column A in Sheet 2

I run vlookups from sheet 1 on the table in sheet 2.

He’s my problem

I have about 1440 rows of data in sheet 2
The vlookup returns values on some of the data but not all. It gives me #N/A

I have cross checked the number formats every which way possible. I have tested it with exact numbers I know that are in sheet 2 but I still get the same kind of error (picking up some of the values I need but returning errors for others). I even reverted to inserting a data validation list in the required cells in sheet 1 (pointing to column a in sheet 2) but even that picks up some of the values and returning errors on others.

I have made sure I’ve locked the range cells required and every other ways vlookup usually falls over but I still get the same problem.
What would be causing this.

CYRIL GEORGENo Gravatar March 4, 2015 at 1:23 pm

at some places VLOOKUP is not working. If the looked up value is not an absolute value i.e. 1074.75, the result is #N/A… Please suggest me what changes I can do in my Excel. I’m using Excel 2007…

Daniel BrewertonNo Gravatar April 3, 2015 at 5:42 pm

One thing to check is the column you are trying to VLookup is set to General.

Andrew TroupNo Gravatar April 14, 2015 at 11:02 pm

I have a VLOOKUP which is returning a value of TRUE, instead of returning the appropriate numerical value from the array it is referencing.

When I re-enter the formula in that cell, it continues not to work, but if I copy the cell’s contents (formula) to another cell, and then drag that cell back over the original cell, it returns the correct numerical values.

So I can fix it, but would like to understand what’s happening. I tried reformatting the cell, as General, as Number, and as Text, but nothing worked. I also tried changing the numerical value in the column being referenced (eg to 0 and to 1) and it did not affect the logical value being returned. I can’t find anything on this problem using Google.

LKNo Gravatar April 21, 2015 at 6:49 pm

Multiplying by one did the trick. Thanks!!

RandyNo Gravatar April 23, 2015 at 5:46 pm


I’m experiencing the exact same problem w/vlookup. Were you able to find a solution?

Martin FNo Gravatar April 24, 2015 at 5:03 pm

In my situation the lookup table is on another sheet (and why wouldn’t it be?) and i was using what appear to be correct sheet references — LUT!table-range — but it just would not work until i put the sheet inside single quotes — ‘LUT’!table-range — even though the sheet name contained no spaces. Why i had to do that i don’t know.

Edd PNo Gravatar May 7, 2015 at 3:44 pm

oh man, thanks. didn’t think about the trailing spaces. fixed!

Shantanu ShuklaNo Gravatar May 27, 2015 at 1:43 pm

Very informative. Thanks for sharing it. My VLookup wasn’t working so I figured it out by following way.

The column # is the position of columns in referenced array.
The columsn which contains answer should be on RHS.

BobNo Gravatar June 30, 2015 at 4:28 pm

Can also be formula recalculation is set on manual.

SusanNo Gravatar July 18, 2015 at 6:22 pm

My VLOOKUP formula works from row 6 down, but not in the first 5 rows. I used reference lock, but the $ dollar signs and ‘ disappeared for some reason. I’m using 2 sheets in the same excel document so the 2007 Excel or 2010 Excel issue can’t be the problem. Please help.

SusanNo Gravatar July 18, 2015 at 6:26 pm

The VLOOKUP formula works but brings back the values from the wrong column. I designated column index number 1, but it brings back 2. Why?

sven ryglertNo Gravatar July 31, 2015 at 8:35 am

Perfect, solved what many other forums couldn’t.

RaziNo Gravatar August 5, 2015 at 2:26 am

I investigated and my spreadsheet had the error described above in reason-2. I fixed and it worked. Thank you very much for sharing these hints.

Tami MillerNo Gravatar August 9, 2015 at 8:02 pm

I am having a different issue, I think. The vlookup only works if I change the source/reference cell to text AND double click in it where it will display the little green error notification on top left of cell – the one that if you click on the “yellow symbol with exclamation” you can find options to fix error. I can double click on every single value in my source column to force that symbol to appear, then it matches. Is there a way to do it to all values at once so I do not have to double click on every one of them?

Tami MillerNo Gravatar August 9, 2015 at 8:03 pm

One more thing – I may have used wrong terminogly – the cell that I am performing LOOKUP on is formatted as TEXT, however, it will only match if I double click in the cell and force the little green triangle and yellow symbol to appear advising there is an error in which I can ignore or fix.

udaypNo Gravatar August 13, 2015 at 12:57 pm

thank you, this saved lot of time..
many thanks for the wright solution..

Mark WongNo Gravatar August 16, 2015 at 8:49 pm

Hi Tami,

If you highlight all of the erroneous cells at once, and then resolve the error for one of them, it should remove the error symbol for all of them. Let me know if that works.


FreddyNo Gravatar August 21, 2015 at 2:49 pm


StephenNo Gravatar August 25, 2015 at 12:56 pm

Also if you have a blank column within the Table_array you’ll get a #N/A

Marco Aurelio de JesusNo Gravatar September 2, 2015 at 11:02 pm

After one day checking everything, I followed your tip: the formula recalculation had been set on manual!
I can’t believe! I’m a hard Excel user! At least I had thought…
Thanks a lot!

MFBNo Gravatar September 10, 2015 at 4:03 pm

Just want to say thank you very much for this, basically just saved me many hours of repeatedly dragging the formula down until it had captured all fields….

F4 is your friend!

DmitryNo Gravatar September 11, 2015 at 3:15 pm

My problem was that the VLOOKUP was looking for 34, but was finding a value next to 8. I could not figure it out. it turned out, I did not insert the “FALSE” at the end of the VLOOKUP formula, and that was the problem. False means – exact match. If you don’t insert FALSE – it defaults to TRUE, which is dumb, and so it finds a “similar match”. I’m not sure how number 8 is similar to number 34, but …. that was my issue.


GeorgianaNo Gravatar September 14, 2015 at 8:16 am

Hello ,

my problem is similar .. but not the same :) . I have received an excel file pulled out from some tool, I don’t know what tool/ application . The issue is this : the column I am trying to use as key element is formatted as 0000-0000-000 , just as I need and exactly the way the reference data looks like . But the data in the cell is not the same, for example :
1234-5678-030 is 12345678030 and 0007-1234-750 is 71234750 . what can I do so that the data in the cell matches the way the cell’s format ?

Gauri ShankarNo Gravatar September 17, 2015 at 5:13 am


Mark WongNo Gravatar September 19, 2015 at 9:55 pm

Dmitry, yes I’ve had this issue before and I agree with you that it doesn’t make sense for them to set that as the default.

Paul EilermanNo Gravatar October 9, 2015 at 11:57 pm

My lookup function is not returning the complete contents of the cell it finds. The cell is the result of a concatenation and can be well over 100 characters

IslamNo Gravatar October 11, 2015 at 10:25 am

Thanks man, this reference lock was ruining my file.

Angela MNo Gravatar October 13, 2015 at 5:08 pm

Easiest-to-understand article on VLOOKUP I have ever come across. Thank you so much!

ChelseaNo Gravatar October 16, 2015 at 7:26 pm

My vlookup isn’t working & I tried all the stuff above. It says that a value isn’t available for the error. Help?

OraNo Gravatar October 31, 2015 at 5:54 pm

Thanks, Mark!
It’s always good to have a checklist for when this arises. Your advice is still helpful years later. 😉

JeffNo Gravatar December 4, 2015 at 6:02 am

Great checklist, found what i was looking for! Appreciate it, your advice never ceases to stop helping me.

KushiNo Gravatar December 13, 2015 at 9:01 pm

Thank you!!

SophiaNo Gravatar December 29, 2015 at 12:42 am

Marriot, Make sure there is no space for “N/A” return. If cell has an invisible space, return value is not valid.

JaskaranNo Gravatar January 27, 2016 at 3:37 pm

Saved me, that little space made the Vlook not work!

Leave a Comment

{ 5 trackbacks }

Previous post:

Next post: