Comments on: 6 Reasons Why Your VLOOKUP is Not Working

The VLOOKUP function is the most popular lookup and reference function in Excel. It is also one of the trickiest and the dreaded #N/A error message can be a common sight. This article will look at the 6 most common reasons why your VLOOKUP is not working. Continue reading →

Comments page 5. Total comments: 223

  1. Hello, Please help me understand why my vlookup formula stops working after 10 matches. My formula is very simple: =VLOOKUP(A7,'compiled responses'!B7:C804,1) my data is very simple: column "a" has numbers, column "b" has numbers

    col A col B
    404523 404523
    447135 447135
    447350 447135 this is where the formula stops working as it returns the incorrect value and then the values become N/A.

    I have checked the data is has no blanks, both columns are general input.

    I look forward to your assistance.

    Kindly,
    DH

    1. I think your VLOOKUP by the sound of it is comparing columns A and B. And in this case it is stopping at that point because it is not a match i.e. 447350 is not equal to 447135.

  2. number could also be stored as text

    1. Very true. The formatting of the values stored and the one searched for must be consistent.

      1. Hi. Thanks for your reply. Unfortunately, it's still not working, even with all values consistent. Totally baffled!

  3. Hi - VLOOKUP is working fine for me apart from only returning the first letter, i.e returning only 'J' instead of John. Any ideas?

    1. Not a clue. VLOOKUP will return all of the content from the cell, so as long as the full name is in there it will work.

  4. HI

    I want to use VLOOKUP with two diffrent spreadsheet which containt no match value on both, in that case is it possible to apply VLOOKUP in this manner??

    1. It is possible to apply a VLOOKUP to two different spreadsheets. Ensure both workbooks are open when you write the VLOOKUP.

      Not sure what you mean by the contain no match on both, but VLOOKUP can help check for matches so sounds like it would work.

  5. Hi Alan...

    Do you have any idea why my VLOOKUP stopped refreshing automatically for ALL my spreadsheets. In your example, when I change I3, I actually have to click into the formula in J3 and hit enter for it to pull the refreshed value.

    1. Hi Carlo,

      I think the cells containing the VLOOKUP's are formatted as text. I would select the cells and check the formatting on the Home tab.

      It may also be that you have manual calculation switched on. Click the Formulas tab and then Calculation Options.

      Alan

  6. Why VLOOKUP with "TRUE" condition is not working on dates?

    1. It should work with dates no problem. Ensure the table is sorted in earliest to latest by the date.

  7. Thank you, the 'VLOOKUP cannot look to its left' was my pain was my issue. Totally unintuitive

  8. VLOOKUP Cannot Look to its Left was my issue.. thanks!

  9. Thank you Alan, you helped me with The Table has got Bigger.

  10. Thank you! My error wasn't any of these, but it was good to be encouraged to look through everything piece by piece. Turns out I had the initial column and the lookup_value formatted differently--one was Number and the other wasn't (somehow...) So, another goofy mistake, but maybe it will help someone.

  11. Why does my vlookup give the same answer?

  12. Hi,

    My table has duplicates in lookup cell, but the value against it is unique, what should I do if I want all the values populated?
    e.g
    Column A Column B
    Banana USA
    Watermelon Brazil
    Banana Columbia

    Now if I want both USA and Columbia in how should I get it, as vlookup only gives USA?

    1. @Gautam Lapsiya Yes the VLOOKUP will only return the first answer.

      An option is to use a PivotTable like in the last example.

      Alan

      1. Thanks Alan

  13. Watched the YouTube video and it was brilliant instruction!

    Saved me loads of time and frustration.

    Thanks so much.

  14. You might be able to concatenate a string. Wrap the * in "" to indicate that you want to refer to that character literally and not as a wildcard.

    Otherwise you might be able to use the EXACT function with INDEX and MATCH instead of VLOOKUP. Information on this can be found below.

    http://www.computergaga.com/tips/lookup_formulas/case_sensitive_lookup.html

    I have not tested this, but think it should work. This function does what its name suggest and ensure the strings match.

  15. That might be. My ERP system only allows * as special characters so all my item number data exports have * in them. Do you know of a way to prevent this?

  16. I am trying to write a VLookup formula to bring on hand data into a sheet that details sales volume. My formula is:: =VLOOKUP(A2,AA$2:AB$100,2,FALSE)

    Item B1007080SHF3MDO2BS***GG is not present on the target range; Item B1007080SHF3MDO2BSR**GG is there, with a required response value of 409.

    When I apply the VLookup to both fields, I get the value of 409 for BOTH, so the 'exact value' part of the formuala does not seem to be working. I have both lists sorted A-Z. It does not seem to matter if I format both columns as text or numbers, same issue (the full data set column does have values that are numbers only & alpha-numeric in both the search criteria and the target range).

    1. It looks like the VLOOKUP is using the ** characters as a wildcard.

  17. Hard to say exactly without seeing the file. Check that there is definitely a match, so no spaces after the number. And also check the formatting of both the lookup_value and on the table_array to check they are the same.

  18. i am doing a very simple lookup and I am getting the #n/a. Trying to pull a number into my master. The first column on both is my ref. the formula is =vlookup(A2,PLANC,2,false). Very very simple. I checked to make sure there was a match and there was and no duplicates were found. Help!

    1. I was having the same issue and just figured it out! (getting #N/A when I know there's an exact match)

      If you see the green triangle on the cell, hover over the yellow exclamation point "Number stored as text". From the dropdown select "Convert to Number" and it will fix it immediately. Good luck!

      1. Well done, Jen.
        Yes, the two values must be the same format.

  19. Thanks for sharing. I had some hidden columns so my VLOOKUP was not working! Fixed it after readong Point 3. on your list.

  20. VLOOKUP won't be able to. It will only return the first name from the sheet.

    You will need more than 1 VLOOKUP. Or a macro would be needed for multiple occurrances of a name.

  21. can any help me put

    i had a sheet of name of the employs and their working hours, here the problem is in my sheet i had two names similar...(EX: mahesh as two time in the sheet )

    when i use vlookup in the table for my sheet i am not getting the second name

    how can i get the second name in the sheet........by using vlookup

  22. Thank you, thank you, thank you for the manual - automatic guide.

  23. Hi Don,

    Having calculation set from manual to automatic is very helpful. Save lots of time.

    Thank you very much.

    Andy

  24. thank you

  25. Hard to say why without seeing the spreadsheet but would expect there to be an error in the lookup value argument. I would check the reference.

  26. Hopefully someone can help me. My vlookup is returning the correct values in my columns but it returns the same value until it comes across another non-zero value. For example the correct value of row 10 is 259 but it will return 259 for rows 11, 12, and 13. Row 14 will correctly show 864 but then rows 15 and 16 will too.

    Has anyone come across this?

    thanks,

    1. Have the same issue. VLOOKUP is used only for values in ASCENDING order! Try Index Match combination instead.

  27. thanks a lot for this information

  28. Thanks a lot! very helpful :)

  29. Sometimes the error may be because it has not calculated properly. If you have calculation set to manual rather than automatic, this can also cause an error when using vlookup or index match. Once the worksheet calculation is set to automatic, it works again (set it to automatic by going to the Formulas tab and then Calculation Operations and set to Automatic).

    1. Massive thanks for this tip! I couldn't figure it out!!!

      1. You're welcome, Anna.

    2. OMG.. thanks so much .. was trying a few times why my excel keep seem to be repeating the same value.

    3. This has saved my hours of hair-pulling ! I have been working with vlookup for a long time now but had not encountered this problem before. Many thanks !

    4. Thank you so much Dan! This annoying setting has wasted me hours before!

    5. Dan,

      THANK YOU!!! Hours wasted, but many more hours saved. The Automatic setting in Calculation Operations did it. Karma credits in the heapful submitted your way.

      1. Thank u Dan.
        I was trying to copy formula and wasted hours. ur just one line comment solved my problem in a tick
        :)

  30. when we using vlookup it is show is same number in all colons

    1. Me too is facing a similiar error.
      Have you got any solution for this.please let me know

      1. save the sheet, it will run the lookup

      2. I'm not sure what you need. Do you mean all columns?
        You would need a VLOOKUP in each. A formula can only return to one cell.

  31. Thank you, it was helpful.

  32. The trapfall of VLOOKUP being not able to "look to right" wasn't known to me before. Very helpful!

  33. Thank you, it was helpful.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)