Top 3 Reasons Why My VLOOKUP Isn’t Working

by Mark Wong on February 21, 2010 · 204 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

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.

{ 199 comments… read them below or add one }

JPNo Gravatar November 22, 2013 at 12:21 am

Great! It is really helpful. Thanks a lot!

LenkaNo Gravatar December 2, 2013 at 12:01 am

My numbers come formatted as text (e.g.’1186815) from a system we use and multiplying by 1 doesn’t help…any suggestions?

abdulsamadNo Gravatar January 23, 2014 at 7:05 am

great help. thanks

MNo Gravatar January 23, 2014 at 11:19 am

My vlookup isn’t working at all.
I’ve checed formatting both sets of data are ” General” format.
But one source is downloaded from Business objects..still not working even though I’ve tried to convert and revert using text, numbers, etc…
Could it be unicode or something?
Also I think there is a vlookup that works with a hypen..(-) that looks at both text, or numbers etc…can an example be posted.
Secondly is there a lookup that is a partial lookup and searches …eg…looks up everything starting with Abc….and searches in the column for this?

Thanks

PRINo Gravatar January 30, 2014 at 10:52 am

THANKS A LOT IT WORKS REALLY.

KallionorppaNo Gravatar February 4, 2014 at 7:59 am

My proplem is that i can’t get any result from VLOOKUP formula. The formula IS OK but For result in a cell I got the formula wide open writed and it doesn’t copy down way wright at all. I have not bressed the “show formula” button. I have to find a match in two text that i can continue my work. I have set the type of cell as an text. This makes me So SO ANGRY!! I have 2000 rows of excel to handle out and this so small thing doest’n work.. GREAT!

LingNo Gravatar February 13, 2014 at 3:06 am

Thanks, this really saved my asses

malcolmNo Gravatar February 14, 2014 at 6:42 pm

Add #4: You have an incorrect range for your table array.

MinaNo Gravatar February 19, 2014 at 6:48 pm

THANK YOU

I was seriously about to throw my computer out the window.

MagdaNo Gravatar February 26, 2014 at 2:58 am

Thank you, thank you and thank you!!! F4 was the solution to my problem!!

shafiq sadikeNo Gravatar March 5, 2014 at 2:03 pm

vlookup dose not work properly

mahmudNo Gravatar March 13, 2014 at 12:08 pm

I HAVE NO APPROPIATE WORD TO EXPRESS MY GRATITUDE. THANKS A LOT!! :D

IsbozethNo Gravatar March 14, 2014 at 6:51 pm

Thank you, was very helpful :)

Regards from Mexico

Cindy BarnettNo Gravatar March 18, 2014 at 3:49 am

Ok, how do you do F4 for a named range? My formula doesn’t work; do I have to have a numerical look up value?
CB

AnnaNo Gravatar March 20, 2014 at 5:54 pm

This was SO helpful! solved my issue!

MarianNo Gravatar March 22, 2014 at 8:46 pm

Nailed it!

JesseNo Gravatar March 27, 2014 at 12:52 am

How do you multiply your look up values by 1?

JesseNo Gravatar March 27, 2014 at 1:10 am

How do you multiply your lookup values by 1?

JamNo Gravatar March 27, 2014 at 3:56 am

The Lock Array was the answer!. thank you

devjmooreNo Gravatar April 2, 2014 at 1:42 pm

Thank you very much!!! You’ve helped me sooooo much.

zainaNo Gravatar April 2, 2014 at 4:08 pm

For #2, you can also use the TRIM formula. It cleans up your text and gets rid of unneccasary spaces.

KevinNo Gravatar April 3, 2014 at 4:03 pm

Found this site when VLOOKUP wasn’t working for me. Found another potential problem – calculations on the workbook are set to manual. F9 to manually calculate, then change the settings to Automatic.

TinaNo Gravatar April 8, 2014 at 5:45 am

This fixed it for me:
the cell where I type my formula was of format “text”
I changed the format of this cell to “general”
then I enter the formula again

marcoNo Gravatar April 8, 2014 at 9:01 pm

Holy cr@p! I multiplied by one and darned if it didn’t work…….all these years I’ve been cutting and pasting values, then hitting “convert to number”…Thanks!

DeeNo Gravatar April 23, 2014 at 2:55 pm

I was able to fix 10,000 rows with above tips. Thank you! But I still cannot manage to get 1000 rows fixed. There should be no reason for this to not work. Now I have to copy and paste 1000 rows. :(

AbhijitNo Gravatar April 24, 2014 at 2:34 pm

I have got a problem while applying the vlookup formula.
when i applying the vlookup as i select the cell of a collumn from first excel.
& when i going to select lookup area from the second excel sheet that i cant select the lookup area. Please give me solution.

SATISHNo Gravatar April 30, 2014 at 7:34 am

Thank you. If followed the absolute method (3 method) after trimming my value and it worked with no flaws.

MartinaNo Gravatar May 1, 2014 at 6:12 pm

I set up my spreadsheet following all of your “must haves” and the Vlookup still gave me #N/A results although the fields were populated in the reference tab. Turns out I didn’t have the spreadsheet SORTED correctly. Once I did that, everything worked. Thanks for getting me started.

JayNo Gravatar May 15, 2014 at 10:23 pm

Only one is #N/A while the others are fine. The reference column is alight based on my check.

b0849No Gravatar May 29, 2014 at 1:24 am

I’m still having the same problem despite using F4… could you help advise please? Need to solve this urgently. Thank!

=VLOOKUP(S63:S3733,’[MASTER BLOCK_CoSTATUS_GracePeriod_status list.xlsx]KCH CO STATUS’!D$2:F$3462,3,0)

AmandaNo Gravatar May 30, 2014 at 12:24 pm

I love you!!! xxx solved loads of problems – thank you

NallymNo Gravatar June 6, 2014 at 9:38 am

Brilliant! Multiplying by one did the trick!! Very annoying even after hitting the number tab the values still stored as text.

jazNo Gravatar June 11, 2014 at 5:26 am

Thanks ,

This solution helped me a lot.

Jaz

SARANo Gravatar June 14, 2014 at 12:57 pm

it’s great thank you

Judy PinnyNo Gravatar June 17, 2014 at 2:50 am

Trim function works when looking up names with a space between. Thanks to the person who suggested that.

SheilaNo Gravatar June 25, 2014 at 3:05 pm

Thanks SO much for your tips. Multiplying by 1 helps for most of my spreadsheet. However, since I’m using the VLOOKUP against a column of jewelry styles, I do have some hyphenated numbers in there. Multiplying by 1 does not work for those. I’d love any additional tips for this particular problem.

SheilaNo Gravatar June 25, 2014 at 3:21 pm

I forgot to mention that some of my styles numbers (including hyphenated ones) have a letter code in them. I’m not sure how that’s complicating things.

Chris GrantNo Gravatar July 2, 2014 at 9:11 pm

VLOOKUP didn’t work consistently for me comparing text strings. The lists were pasted from two difference places. I eventually discovered that the problem was the character. In one of the lists, there were apparently two different versions of a space. Once I made them all the same, VLOOKUP worked perfectly.

How did I know that there were two character versions? I did a search and replace for each column, trading ” ” for a nonsense string like “)))”. After that, I could see what looked like spaces still in the string.

I carefully highlighted the persisting empty space (the size of one character) and pasted it into the “search” part of a search-replace dialog box, with “replace with” as a regular space character.

I don’t know what that character was, but I later learned that there are 20 different unicode space characters.

Chris GrantNo Gravatar July 2, 2014 at 9:13 pm

Hmm. The editor is replacing the string “” with an actual space, so my third sentence doesn’t make sense. It should read “I eventually discovered that the problem was the -space- character”

KaranNo Gravatar July 10, 2014 at 2:25 pm

Thanks!

TiagoNo Gravatar July 23, 2014 at 10:17 am

Thank you! You gave me a big help, great job.

FNo Gravatar July 30, 2014 at 3:56 pm

@M Check that your ID column is the first one in the range of values.

MichaelNo Gravatar August 1, 2014 at 1:28 am

Great Tips, I’ve been stuck on this for a week before stumble on this page.

My Problem:
2. You Have a Trailing Space at the End of Your Values – See more at: http://www.randomwok.com/excel/top-3-reasons-why-my-vlookup-isnt-working/#respond

Thank-You!

Adele de WaalNo Gravatar August 12, 2014 at 10:38 am

When I apply the vlookup function I select the cell of a collumn from the current excel sheet. When I go to select the area from the second excel sheet I cannot select the lookup area. Please give me asolution.

CathyNo Gravatar August 16, 2014 at 4:13 pm

THANK YOU! I was so confounded and frustrated!

krishnaprasadNo Gravatar August 20, 2014 at 4:24 pm

DEAR SIR,

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

n.k.prasad

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!

Leave a Comment

{ 5 trackbacks }

Previous post:

Next post: