Comments on: Excel VLOOKUP not working - solving #N/A and #VALUE errors

The tutorial explains how you can quickly cope with VLOOKUP not working problems in Excel, troubleshoot and fix common errors and overcome VLOOKUP's limitations. Continue reading →

Comments page 3. Total comments: 228

  1. That has really fixed my problem after long time searching, Thank you !!

  2. The numbers formatted as text gets me every time

    1. Hello Pat!
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  3. Hi
    How do I get the 0 to return as text in my formula, this leads on to pivot table and if it can find the value it presents as text but if it cant it presents as value so I get two lines in my pivot? =IFERROR(VLOOKUP(A2,'[Works in RAMM by month 19-20.xlsx]Sept to June 20'!$A$1:$L$276,12,FALSE),"0 ")
    thanks Mo

    1. Hello Maureen!
      If I understand your task correctly, maybe the following formula should work for you:

      =IFERROR(VLOOKUP(A2,'[Works in RAMM by month 19-20.xlsx]Sept to June 20'!$A$1:$L$276,12,FALSE),"0" & " ")
      Hope you’ll find this information helpful.

  4. i am having a lookup related problem when i am applying lookup function to a cell which does not contain the actual data but there is formula applied to get the value from other two cell so now lookup is unable to look for that cell where formula has been filled but when i am typing actual value in that cell lookup function is working correctly but when there is a formula which is showing correct value lookup function is unable to work .
    For example lookup can look for a value 3 and search it in an array but when there is two column containing 1 in first column and 2 in second column and now in third column i am using formula to add them simply so i sum them and now the value i am getting is 3 in third column but when i am applying lookup function to it. It is showing #N/A as usual but when instead of formula i am directly typing the value 3 in third column the lookup formula works perfectly so any solution on this will be helpful.

    1. Hello Rajat!
      I need more information on your problem to help you.
      Most importantly, what function are you using for a search? Is it VLOOKUP or anything else? Please give me the formula which is performing a search.
      What data are in the cells which that formula is referring to?
      Please describe your search in more detail, and I will be able to help you.

      1. Hello Alexander,

        I have a similar problem like Rajat. The 'lookup_value' reference in my Vlookup function is actually a cell with formula which is =RIGHT(A1,3). But vlookup is unable to read this and returning #N/A error, if I manually put the result of =RIGHT(A1,3) then vlookup is able to return the result. Also please note that if the result of the function =RIGHT(A1,3) is text then it work but unfortunately my result for this function is a number, so I guess this has something to do with formatting of the cell or something.

        GK

        1. Hello!
          Calculation result RIGHT(A1,3) - always text. If you need to search for a number, then I recommend using = VALUE(RIGHT(A1,3))

          1. I had the same problem,

            Thanks

          2. Hi

            Working perfectly!!! Thank you so much!!!

            GK

  5. hello,

    can you help me_ please. This function =IFERROR(SVYHLEDAT(A3;'M (3)'!A3:I9988;3;1);"Not")
    do not describe. Why?

    Thanks

  6. VLOOKUP() will fail if, "the lookup column is not sorted in the ascending order." Well, that's just stupid.

  7. This is very helpfull.

  8. When using vlookup, I can get the correct results for most of the table. When I get down to the last 4 lines of the table, it gives me results from previous lines when i change the lookup data. I have resorted the table multiple times but always get the same incorrect response.

  9. I am using vlookup to copy values from one table to another. Many of the values have been copied, but not all. Those that have not been copied are showing #NV. I have checked for all the possible reasons for the error, corrected some, but the problem persists and has not changed at all. I even tried INDEX/MATCH with no avail. Any Idea what could be the cause?

  10. Thank you for this, very helpful!

  11. Thanks for this outstanding reference, much appreciated! Also, good to know I'm not the only one that struggles with this in Excel.

  12. When using vlookup, the formula only displays moot the value. No error messages. Any ideas?

  13. Hi, i use vlookup to match in the completion status from another file. but then some of my cell does not capture the formula and turn out the result is wrong. would anyone please help me out in this matter.

  14. i am using vlookup from another sheet, i have entered 3 entries that is ok but when i am doing next entry it pulls value from wrong row. please help me i have my file if you ask me i can mail you my file.

  15. Another possible issue: special characters in lookup value. E.g. tilde ~. Solution would be to duplicate this character: VLOOKUP(SUBSTITUTE(C1,"~","~~"),A:B,2,false)

  16. excellent ,thank you

  17. Trying to do a vlookup on a table that is 1000 rows and 26 columns. If I try to get the value in the 13th column, it works fine. However, if I try for any column greater than that I get a #REF! error. All columns have valid data. Formula is =VLOOKUP($a1,TableSheet!$A:$Z,15,FALSE). Any ideas?

    1. If you want to get work done, and run into excel misbehaving, go find an old copy of Lotus 1-2-3, install it, copy your data in and do your lookups there. The Lotus vlookup function isn't nearly as stupid or sensitive as excel, and doesn't require the lookup table data to be sorted just so to work properly.

  18. I have data in two different excel files. I tried to input VLOOKUP manually.
    After inputting =Vlookup(A2, I tried to jump to other file and received error for formula correction.
    Can you please help me to resolve this issue?

  19. =VLOOKUP(IF(BC10>=130%,"130% & Above",IF(BC10<70%,"0.0 - 69%",ROUNDDOWN(BC10,2))),INDIRECT(BL10),2,0)
    so the result should show rating
    Sales Target RM25mil & Above
    Rating Rating Range Ach % Rating Ach %
    Outstanding 4.75-5.00 126% - Above 5.00 130% & Above
    4.94 129%
    4.88 128%
    4.81 127%
    4.75 126%
    4.50-4.74 121% - 125% 4.74 125%
    4.68 124%
    4.62 123%
    4.56 122%
    4.50 121%

  20. Very Useful - keep up the good work ( share the knowledge..sharing is caring )

  21. THANK YOU. THIS IS QUITE USEFUL.

  22. The iferror function work greatly, the if(iserror way may still produce #VALUE! cells.

  23. I did a vlookup and the output showed this number (15359E+11) instead of the actual digits. I have not had this kind of error before. Please advise.

    1. Just convert it to NUMBER

  24. Hi,

    I have problem with vlookup, if i perform the vlookup in the same file with different sheets, the formula is giving me partial results. However if i vlookup the same data from a different excel file, its giving me the complete results.

    what would be the reason, Is there something i can do with the formula to work perfectly within sheets?

  25. =IF(K9="one",IFERROR(VLOOKUP(N9,[One.xlsx]Ledger!$A$12:$BI$29,3,FALSE),"No Data"))&IF(K9="two",IFERROR(VLOOKUP(N9,[Two.xlsx]Ledger!$A$12:$BI$29,3,FALSE),"No Data"))&IF(K9="three",IFERROR(VLOOKUP(N9,[three.xlsx]Ledger!$A$12:$BI$29,3,FALSE),"No Data"))

    Result FALSEMANISHA BISTAFALSE

    how to remove FALSE

  26. Hello.
    I have an issue with a vlookup that, I am certain is structured correctly.

    When I double click to have the cells auto-polulate with the vlookup formula, most calculate correctly. However, a handfull will calculate as #N/A. When I look at the formula, somehow the second parameter has changed to exclude a portion of the array or table.
    This =VLOOKUP(D3,A3:B22,2) should read like this =VLOOKUP(D3,A1:B22,2).

    What can I do?

  27. I am having problems with my Vlookup, the trouble shooting I have done is making sure the lookup value lengths are the same on both spreadsheets and the formats of the lookup cells are the same, I have also done an error check on both spreadsheets everything should be working fine but the formula result is not showing. What could I be doing wrong? =VLOOKUP(TRIM($A2),Sheet1!$A:$P,8,FALSE) this returns a 0 value, if I change false to true no value and it should be returning a value of 160.

  28. I am having trouble with my Vlookup after I had got it working. I'm not sure if the list is just too big or formatted wrong. I have it in the left most column searching a different sheet. I'm updating a mailing list with a list I already have and pulling from unique numbers that are between 5 and 6 digits. For whatever reason these digits are no longer working for the vlookup. I have tried to make them text and number and it just won't pull any data no matter how small I make the vlookup. Even just 3 cells that do contain the numbers. I'm am at a loss. I can't make it work, even though it should work.
    =Vlookup(B2,Sheet1!B2:Sheet1!F10,2,FALSE) even with "TRUE" it isn't working. I tried Hlookup as well. (I have a list of over 35k, should I just make smaller lists?)
    Is it because of the numbers being between 5 and 6 digits?

  29. Ugh, sorry for the bother, I found the issue. I had corrected the spelling of scullcap from skullcap in the table but hadn't resorted it.

  30. I am using Microsoft Excel on a Mac. I've used VLOOKUP for years in very complicated models with no issues. I am now stumped: I have a simple table that works for VLOOKUP for all words except those that begin with "Se" up until words that begin with "Si". So if I look up values that go with Sammy, it's fine, with Stan or Tiger, all fine, but it just returns zeroes if I look up values that go with Selma, Shark, Siam, etc. What gives?

  31. A note on the #NAME explanation above, where you say you must have misspelled the FUNCTION name. This is not necessarily true, other circumstances where this error might include:
    - Where you misspelled the name of ANOTHER function (eg a nested function you're using to produce the lookup value)
    - Where you misspelled the "Exact Match" argument, eg providing "F" instead of "FALSE"

  32. Hi there,

    When trying to do a Vlookup to another file my it is not allowing me to even select the table array I want to search. why is this happening?

  33. When changing the column from 10 to 11, the formula returns #REF. There is a value in column 11 and it's formatted exactly the same as column 10. The formula works from columns 1-10, but does not work from column 11 to the end of the spreadsheet. Can it not look farther than column 10?

  34. Hi Svetlana,
    In my case the error was because of a simple whitespace. Make sure that the value in the lookout column didn't have any white spaces. To remove any white space Just select all the values and hit Ctr+H and replace " " with just "". Took quite a while to figure out.

  35. Stephanie:
    I don't think the cell format is the issue.
    These are my first thoughts to check.
    Check to be certain the data you want VLOOKUP to use as a reference is in the current sheet's A2 cell.
    Is this current workbook's name THRESHOLDITEMLISTING5918.xlsx?
    If not, can you access this Thresholditemlistings workbook?
    Does it require permissions you don't have?
    Does Sheet 1 of that Thresholditemlistings workbook contain the list of info you're referencing?
    Is the data in A2 through G818?
    Is the data in the first column of sheet 1?

  36. Trying to use VLOOKUP with cells that contains both text and numbers.

    Example: AU0002

    I tried changing to general, text, and number and still will not work with the VLOOKUP.

    Formula: =VLOOKUP(A2,[THRESHOLDITEMLISTING5918.xlsx]Sheet1!$A$2:$G$818,1,FALSE)

  37. Iwant to use a range name in lookup, but use a cell reference to a formula that defines the range name. For instance:

    Cell u5 =LOWER(CONCATENATE("f",H5,I5)) returns range name fws

    and cell v5 =VLOOKUP($W5,fws,1,1) contains cell reference to u5 so I don't have to enter the range name fws in the lookup formula

    but it doesn't work. Is there any easy fix?

  38. Dear Team,

    I have an issue in Vlookup, i am doing vlookup in my laptop but it is taking same values for all, please help me how to salve. The below is the example.

    Site ID Site ID with job numbers
    ZRI584 J-ZRI584-0000115
    ZRI584 J-ZRI584-0000115
    ZRY072 J-ZRI584-0000115
    ZRW958 J-ZRI584-0000115
    ZRW958 J-ZRI584-0000115
    ZR3106 J-ZRI584-0000115
    ZRU170 J-ZRI584-0000115
    ZHMA003 J-ZRI584-0000115
    ZRS996 J-ZRI584-0000115
    ZRS996 J-ZRI584-0000115
    ZJE043 J-ZRI584-0000115
    ZRH682 J-ZRI584-0000115
    ZRU176 J-ZRI584-0000115
    ZRS857 J-ZRI584-0000115
    ZRU173 J-ZRI584-0000115
    ZJE026 J-ZRI584-0000115
    ZJD484 J-ZRI584-0000115

  39. Vlook Up is skipping identical data with no change in formula. Vlook Up entered at column AO. Referencing column AL and pulling from column 2 of array (which contains Program name) located on different tab of same file. The NA77BIO reference is unchanged, yet #N/A is returned further down in the sheet.

    =VLOOKUP(AL20022,'2017-18 National Accounts (2)'!8:163,2,FALSE)

    NA45STL 002 ACTIVE NATIONAL STERLING INC./SIGNET
    NA77BIO 002 ACTIVE NATIONAL BIOMAT
    NA77BIO 002 ACTIVE NATIONAL BIOMAT
    NA77BIO 002 ACTIVE NATIONAL BIOMAT
    NA77BIO 002 ACTIVE NATIONAL BIOMAT
    NA77BIO 002 ACTIVE NATIONAL BIOMAT
    NA77BIO 002 ACTIVE NATIONAL BIOMAT
    NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK
    NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK
    NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK
    NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK
    NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK
    NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK
    NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK
    NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK
    NA00SRS 002 ACTIVE NATIONAL SEARS / KMART
    NA00SRS 002 ACTIVE NATIONAL SEARS / KMART
    NA00SRS 002 ACTIVE NATIONAL SEARS / KMART
    NA00SRS 002 ACTIVE NATIONAL SEARS / KMART
    NA00SRS 002 ACTIVE NATIONAL SEARS / KMART
    NA00SRS 002 ACTIVE NATIONAL SEARS / KMART
    NA06KOH 002 ACTIVE NATIONAL KOHL'S
    NA06KOH 002 ACTIVE NATIONAL KOHL'S
    NA08CFA 002 ACTIVE NATIONAL #N/A
    NA47GMS 002 ACTIVE NATIONAL GOLDMAN SACHS
    NA47GMS 002 ACTIVE NATIONAL GOLDMAN SACHS
    NA77BIO 002 ACTIVE NATIONAL #N/A
    NA77BIO 002 ACTIVE NATIONAL #N/A
    NA77BIO 002 ACTIVE NATIONAL #N/A
    NA77BIO 002 ACTIVE NATIONAL #N/A
    NA77BIO 002 ACTIVE NATIONAL #N/A
    NA77BIO 002 ACTIVE NATIONAL #N/A
    NA77BIO 002 ACTIVE NATIONAL #N/A
    NA77BIO 002 ACTIVE NATIONAL #N/A
    NA77BIO 002 ACTIVE NATIONAL #N/A

  40. I have the following formula to pull column 33 from the URL spreadsheet specified. I get the #NA, Did not find value in vlookup evaluation.

    =VLOOKUP(E1,IMPORTRANGE("1EG2Qcodvx1IkbUGbJRuBKHoOYtMLRCY5a73_8uWI","SEPT!$A$1:$AG"), 33, 0)

    I also tried this:

    =transpose (query(importrange("1EG2Qcodvx1IkbUGbvQeJRuBKHoOYtMLRCY5a73_8uWI","SEPT!$A$1:$AG"),"Select Col33 where Col4 = """&E1&""" ",FALSE)) and it pulls in the correct column info, but it gives me the cell above the one referenced. In other words, I am looking up the email address to return the total number of available days and if it is email address on row 9, it gives me the email address for row 8.

    Do you have any suggestions?

    thank you

  41. I've had a few #NA errors in the past but they can be corrected in the following fashion

    1) if a vlookup returns NA and you need to populate a cell with a number use as follows:

    "=IF(ISNA(VLOOKUP(D1,A1:B3,2,0)=TRUE),0,VLOOKUP(D1,A1:B3,2,0))

    a 1000 cell d1 = d
    b 2000
    c 3000

    2)

    if a vlookup returns NA and you need to populate a cell with a piece of text use as follows:

    "=IF(ISNA(VLOOKUP(D6,A6:B8,2,0)=TRUE)," ",VLOOKUP(D6,A6:B8,2,0))

    1 a cell d6=4
    2 b
    3 c

    The following formula returns a blank cell

  42. Thank you I couldn't get my VLookup to work and your article worked.when they work its brilliant when they don't your tearing your hair out. Great article.

  43. You're a lifesaver, thanks!

  44. THANK YOU! I learned a valuable lesson and was able to solve my problem!: "Always use absolute cell references (with the $ sign) in table arrays, e.g. $A$2:$C$100 or $A:$C."

  45. Hi,

    hope you can help. I am using the vlookup function from an exported spreadsheet. the cells in the column are a combination of of text or dashes and some appear as #NAME? When I click into the cell, the actual words appear but not when you look at the column. excel is reading it as a formula but it is not.

    How do I fix it? I have searched for answers but not able to find a solution.

    thanks

  46. Hi, Will you be kind enough to explain following formula

    =IFERROR(INDEX($B$4:$F$15,MATCH(1,(INDEX($B$4:$F$15,,MATCH(L$10,$B$3:$F$3,0))"")*($A$4:$A$15=$K11),0),MATCH(L$10,$B$3:$F$3,0)),"")

    I would appreciate if part by part formula is explained

    Thanks in advance!!

  47. Hey All,

    I have been looking into a few options to try and rectify a lookup I am doing which pulls from another tab. Currently I can replace the blank cells which are showing as 0 with the following formula, however I now also need to eradicate n/as that show when the lookup returns no match against the cell targeted. Please see below:

    =IF(VLOOKUP([extract]],Table13[[Num]:[extract1 ]],13,FALSE)="","",VLOOKUP([extract],Table13[[Num]:[extract1]],13,FALSE))

    The above works fine, however returns n/a's. I have tried to use "iferror" without success. I need to exclude both 0's and n/a's

    Thanks.

    1. In Excel 2013 you can go do the following to remove the zeros:

      File>Options>Advanced>Display options for this worksheet: (use the drop down arrow to select the correct worksheet if more than one in your file).

      Make sure that the following option is deselected: Show a zero in cells that have a zero value

      Good luck with the #N/A issue.

      1. You can also try the following for the "N/A issue:

        File>Options>Formulas>Error checking rules:

        Make sure that the following option is deselected: Formulas referring to emtpy cells

  48. Good solutions..i'm really happy on seeing your solutions n solve many issues easily:) :)

  49. i have a spreedsheet that i am running the following function thru =REPLACE($D344,1,2,VLOOKUP(LEFT($D344,2)+0,$AB$3:$AF$14,2,0)). Column D contains 4 digit values, ranging from 3700-37DF, i do have a few cells in coulmn D that contain 3A00, 3B00, 3C00 and on these cells i am getting #VALUE! errors. can someone help?

  50. Hi,

    In vlook up is it possible that value can be increased in row?
    Exp
    =Vlookup(A3,a2:C8,2,false)
    And in second row
    =Vlookup(A3,a2:c8,3,false)
    ,2,&,3, value increased with dragging

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 :)