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

{ 187 comments… read them below or add one }

← Previous Comments

Great! It is really helpful. Thanks a lot!

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

great help. thanks

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

THANKS A LOT IT WORKS REALLY.

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!

Thanks, this really saved my asses

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

THANK YOU

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

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

vlookup dose not work properly

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

Thank you, was very helpful

Regards from Mexico

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

This was SO helpful! solved my issue!

Nailed it!

How do you multiply your look up values by 1?

How do you multiply your lookup values by 1?

The Lock Array was the answer!. thank you

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

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

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.

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

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!

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.

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.

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

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.

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

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)

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

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

Thanks ,

This solution helped me a lot.

Jaz

it’s great thank you

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

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.

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.

← Previous Comments

{ 5 trackbacks }