Top 3 Reasons Why My VLOOKUP Isn’t Working

by

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.

275 thoughts on “Top 3 Reasons Why My VLOOKUP Isn’t Working”

  1. Thank you so much iv been stuck with my vlookup for the past two weeks and the second solution fixed my problem! you saved me!

  2. Hello all,

    I wanted to share a solution I came upon that may help. I was having an issue using MATCH, and stumbled upon this website during my Googling. The solution I came up with was to change my formula slightly, such that I could use ‘Less Than’ or ‘Greater Than’ as opposed to ‘Exact Match.’

    e.g.:

    =MATCH(100,A:A,0) vs. =MATCH(100.001,A:A,1)

    The former returned N/A, while the latter returned the appropriate answer. I imagine the issue was related to the formatting of the number, but I was unable to solve it. This is not very elegant, but a solution nonetheless.

    -Cory

  3. Go to File/excel option/formulas/calculation option/workbook calculation/ and click on automatic. and your problem will be resolved within seconds. I faced this problem many times bt a last i got the answer. its magic.

  4. Thank you for the “space” trick!!! Have used vlookup for years, but didn’t know about this one. You are a life-saver!

  5. Pingback: VLOOKUP - LA INNOVATIS - Consultoria de Gestão e Desenvolvimento de Software

  8. As a note, for problem #2, you could also use trim().

    “Removes all spaces from text except for single spaces between words.”

  13. Okay im officially frustrated … I am using vlookup to reference to a sheet that has been imported from another file. Everything worked fine about 2 weeks ago now my vlookup is no longer working. I have tried all 3 of your solution and nothing. I am at a loss. I have cleared the formulas and replaced them and nothing

  15. I think another error worth mentioning is that there are hidden columns in the lookup array 🙂

  16. What is the problem with below formula?

    =IFERROR((VLOOKUP(B4,’O:\ITCM Project Libraries\Projects\10083\Defined Project Deliverables\12 Testing\04 Deliver\06 Requirements\Requirement Catalogue\[TestQueries_10417 RWA Infrastructure – Requirements Catalogue v1.11.xlsx]T&C_RWA Infrastructure & PR’!$B$4:$R$366,18,FALSE)),””)

  18. Every time I look at this I’m thinking I haven’t made one of these errors and every time I have. Thanks a ton.

  19. i did vlookup from 1 sheet to another sheet it’s not coming it shows #NA. please give me solution do i have to change anything in the sheet. before going to lookup?

  21. why it will be that the v-Lookup doesn’t read the other spreadsheet I am looking up or matching

  25. having been struggling to figure out why my vlookup foumular does to pick up the return value instead it shows N/A. Finally got it solution 2 . Great help

Leave a Comment