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 7. Total comments: 228

  1. In certain parts of my worksheet , when i enter the vlookup function it doesnt do anything it remians as text format

    2. Hello, Mahir,

      Please make sure you enter the equal sign before adding the formula.

  2. Svetlana

    It seems you may be able to help me. I am using Excel 2010. I created a drop-down list, and want to use VLOOKUP to complete other columns, with information in the table which I used for the drop-down list. The column for the drop-down is A.

    If I make my selection from the drop-down list, nothing happens, that is my formula, e.g:=VLOOKUP($E2,Premises!$A$2:$P$101,4,FALSE) remains in the cell, with no value provided. I checked formatting of cells to make sure it is the same.

    Thanks
    Jacoba

    1. Hello, Jacoba,

      To understand your task better, we'd like to have a look at your data. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  3. Thanks Svetlana

  4. Thanks for your explanations. The problem with my vlookup was the formatting of the "search" data; formatting it as "number" solved the problem!

  5. Hi, svetlana cheusheva
    i have a query related to excel please help me
    how to highlight past dates?
    Hope you help me!

    1. I think Formula is =a2<today() it is work.

  6. I keep having my vlookup column doing this. Is there something I have to do to correct this?

    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)
    =VLOOKUP(A4,October!A:A,1,0)

    1. Hello, Dee,

      You can add a check for the found value:
      =IF(ISERROR(VLOOKUP(A4,October!A:A,1,0)), "",VLOOKUP(A4,October!A:A,1,0))

      1. WHATS with wrong (( ? )))

  7. your utmost consideration and accommodation regarding my request would be of great help...please...

    thanks

  8. Bank Code Legend:
    bpi BPI

    BEGINNING BALANCE 258,581.64
    ADD:
    CASH DEPOSIT 270699
    A/R - COLLECTIONS 530,400.00
    FUND TRANSFER IN 0.00
    TOTALS 801,099.00
    DEDUCT:
    CASH REMITTANCE 0.00
    FUND TRANSFER OUT 0.00
    EXPENSES 414,350.00
    TOTALS 414,350.00

    ENDING BALANCE TO DATE 645,330.64

    good day!
    i just wanna know how can i show/flash the names of clients (which is in the other sheet) who paid cash in my report? same with the client who paid their accounts receivable, which i termed "collection"?

    i just want to show my boss the names of clients who paid their obligations (cash payment & collection of A/R).

    i used vlookup to find the details about the "bank" (bpi) the beg balance, the cash deposit, collections etc. what i want to solve now is how can i make the names of the clients appear in my report to see in details who the clients are already based on the data i encoded. and if my boss tries to see the details of another bank, same thing i want to appear the names of clients i encoded, also..
    please help me..

    Thanks!!

  9. Bank Code Legend:
    bpi BPI

    BEGINNING BALANCE 258,581.64
    ADD:
    CASH DEPOSIT 270699
    A/R - COLLECTIONS 530,400.00
    FUND TRANSFER IN 0.00
    TOTALS 801,099.00
    DEDUCT:
    CASH REMITTANCE 0.00
    FUND TRANSFER OUT 0.00
    EXPENSES 414,350.00
    TOTALS 414,350.00

    ENDING BALANCE TO DATE 645,330.64

  10. If nothing else works for number search (i.e. excessive blanks removed, data formats made the same number format) this works for me:

    =vlookup(A1/1,D1:D100,2,FALSE)

  11. Hello, this post helps to reduce time on vlookup formula, thanks

  12. I am using a vlookup to match sales and on hand data for item numbers that contain an'*' in them. The lookup is working except for occasion where the exact value is not found, but something close is found. I am told that the wildcard '*' is the reason the formula brings back a bad result even when the vlookup uses the FALSE condition (=vlookup(A1,G1:H13000,2,FALSE. The target looks like B110015BS***GG but pulls from B110015BSR**GG. Does anyone know how to make the formula ignore the wildcard? I tried doing a find/replace, but as a wildcard, it replaced the whole string in each field..

  13. SL No Part # Description SL No Part # Description
    1 1100316 PLATE SPRING UPPE 1 1100316 #N/A
    2 1100611 KEY WOODRUFF CLUTCH 2 1100342 #N/A
    3 1100342 BEARING BALL 3 1100611 #N/A
    4 1101008 ROLLER 4 1100818 #N/A
    5 1101205 SPRING RETURN 5 1101008 #N/A
    6 1180123 BALL STEEL 6 1101205 #N/A
    7 1100818 WASHER BRASS 7 1180123 #N/A
    __________________________________________________________________
    I have checked all the columns if the

  14. may anyone help me..? during using VLOOKUP i am facing problem, actually i have a data which have extra spaces and "" quotes etc in table array but showing simple as lookup value and obviously i am unable to get the data so pls help me..

  15. Hello,

    Excellent post! Out of all the pages I looked at to resolve this problem, yours worked. So simple and Boom!!

  16. I was wondering if you could help me with an issue I have been having. I pull daily spreadsheets with information for employees. I have a column at the end that has my notes for each individual case. With each spreadsheet I use vlookup to transfer the most current notes from the previous day’s spreadsheet to the one I just pulled. The lookup value I use is the employee’s ID number which I always remember to convert to number. Every time I transfer the notes, the majority are correct but quite a few either populate as ‘0’, ‘#N/A’, or pull previous notes from much earlier spreadsheets. I’ve been on numerous excel forums looking for a solution and have tried many(locking the reference table array, sorting and filtering each spreadsheet the same way, etc) and none seem to work. I really appreciate any insight you can offer, correcting each spreadsheet has been a timely process that I feel I can be better spending doing other activities.

  17. I have a list of members per month for three months and have to combine all the members of those months and mark in front of the members who were present in that perticular month. I know that it can be done by vlookup but i am not able to do it.

    Please help.

  18. I am trying to do Vlook up and it keeps on giving me #n/a
    It is reading from 2 worksheets within one document;
    vlookup($A3,'Venue Options'!$A:L,2,FALSE)
    I have checked that my columns are in ascending order and that the cell format is set at number....
    I am at a loss! I know it is difficult to see what I am doing but this is a relatively easy formula. ..
    Thank you
    Ali

  19. hi,

    when using the following formula =VLOOKUP(A:B,Sheet2!A:C,3,2) getting value error. pls help.

  20. hi,

    I have an assignment, using vlookup and if functions,
    we cannot use the "if error" to not shown the "n/a",
    what function I can use instead?

    Thank you

    Cris

  21. Every time I enter a vlookup lately (I didn't use to have this problem), if returns something like =VLOOKUP(A:A,'[Restricted Detailed Employee Listing_4.14.2015_limited.xlsx]Detailed Employee Listing'!$A:$S,false,19). Please help.

  22. How can I search the numbers with E+ in my databases of thousands records.

  23. How can I search the numbers with E+ in my databases of thousands records

  24. VLOOLUP not giveing correct number.
    Never mind. I found the problem. There were duplicate values and it grabbed the largerst value

  25. Hi there, I would appreciate your help.
    I created a table of VLookup formulas. Worked great, but now I am trying to sum values in several rows and I get a Zero.
    I would also like to average, counta and several other functions but it doesn't seem to work on the values created by VLookup.
    What am I doing wrong.
    Thank you so much for your help.

  26. I am using excel 2013. when I try to do vlookup,the first row prints correct value. when I drag the formula for other columns it prints the same first value for all the other columns even if the formula is changed.

  27. I have been using a the same Lookup in a large spreadsheet for about 4 years now. Recently I have been getting random results. In some of the cells I get the expected results but in others I just get a 0, not N/A or #Value#. I've made sure that my table is sorted in the correct order and that the data I'm looking for is in the furthest left column. I've also made sure that I have the correct range in my formula, nothing seems to help.

    What could be causing this random error in my results?

  28. Brilliant, Thanks a lot, I've searched high and low for an answer and this is the only one that I can understand and make work.

  29. I have a spreadsheet that we update daily on one tab and we use a vlookup to update information on the main tab. The problem is that there are several client names that won't update automatically after we paste the new data. We have to clear their name each day and retype it for the vlookup to work. I have ran trim and that doesn't work. Plus my colleague cannot even update the name. The vlookup won't work at all if she updates from her excel. The names are in alphabetical order. The range is covered. THe names aren't all at the end they really have nothing in common that I can see. It seems completely random. If I retype their names the vlookup does not return the #NA but if my colleague retypes their names her Excel does not update the data. I have gone crazy trying to figure this out. Help!

    1. We detfniiely need more smart people like you around.

  30. The problem I frequenly run in to is the data that is being displayed by the Vlook up is both correct and incorrect. It pulls data from the wrong cell. Im pritty sure I set everything up correctly but why would it present incorrect data why not just N/A or error? In addition some of the data is represented properly. Seems sporadic on one sheet driving me nuts.

    1. Hello,

      Most likely the issue is in your data or in the way they are presented: the cells format, excess spaces, merge cells, etc.

  31. THANK YOU!

  32. I have an Excel 2010 document that has vlookups and other formulas. I want to share the Excel doc so that my team can update their parts and we can all be in the file. The only problem I am having is that when the document is being shared and I go to update it with new data (I am the only one on the document when I add new data) for everyone to update the vlookups stop working. What can be causing this issue?

  33. 1- Can i use vlookup formula to search for data in an excel sheet that has blank rows or columns?

    2- can i build my vlookup formula on the right of the data that I need to search it?

    thanks,
    Tony

    1. Hi Tony,
      1) yes, the VLOOKUP formula can be used to search through blank rows/columns.
      2) yes, it's up to You where You place your formula.

  34. i what to compare my list of names, Numbers, dates in 2 sperate excel spreadsheet for each of them to finds erros.for example, i want boths spread sheet to have save figure 1245 and 1245. it if any contain 1254 then how can i corret it. example 2, i want stanley on both spreadsheet, it the ohter contain stanely, i can excel tell me the teh mistake and how can i fix it.

    can anyone help me please.

  35. Hi Sveltana,

    My problem was a silly one my lookup search column wasn't sorted so it was returning a few N/A# but now it's solved. I owe you one and you got my gratitude it was really appreciated.

    Thank you very much for posting this is really useful.

  36. found the answer:
    add Iferror to your formula:

    =IFERROR(VLOOKUP($R2;'my database'!$E:$U;6;FALSE);" ")

  37. This is very helpful! Thank You. Unfortunately, my VLOOKUP is still not working and I'm receiving the #N/A. I know what the issue is, but can't fix it. I am looking in a range that doesn't contain the VLOOKUP value I would like (which is ok) and I just want it to return to zero (0). Is there any way I can do that in excel?

  38. I have been struggling for hours , you saved my life. I would have never guessed that the lookup column had to be the leftmost column of the table array..

  39. It's just not working. I used the wizard to build the formula =VLOOKUP('Tracking In Progess'!A:A,A:A,1,'Tracking In Progess'!A:C) and it always returns #VALUE!-- I tried swapping the lookup_value and table_array and it always returns #VALUE! I've tried doing this with five different workbooks with different data. It never works. I've tried it in 2010 and 2013 and the result is always the same. I need this function -- but I hate it!

    1. It works now that I followed Derek's comment.

  40. Hi Chris,

    It's hard to determine the cause of the problem without seeing your data. Anyway, this is not because of Excel limitations. Even in older versions of Excel, a limit to the number of items displayed in drop-down lists was 1,000. In modern versions it's 10,000.

  41. Please help!

    Disclaimer: I'm a newbiew with excel. I've created a table which has a couple hundred items which is referenced to by a vlookup. For some reason, when I am using the drop down to select an item, the drop down only shows 196 items from the table. Nothing more. The table array in the formula is correct and locked. BUt for some reason the dropdown list will only go so far. Help?

  42. Thank you so much for your Manual uploaded

  43. Dear,

    thank you for the great information , but I review all the possible problem and I fix them , but steel #N/A! and the Looked up value is exist!!

    so here is the lookup: =VLOOKUP(A2,C2:D1048534,1,FALSE)
    the column A have numbers which I need to make sure if it's exist or not then give me the value for it's reference. and C, D are the array index.
    and I used False because it's phone number I need the exact match.finally 1 will return the subscription date.

    they both have the same type , I check with =TYPE() , also they are match I checked with A2=D3.

    :) so how can I fix it?
    thx

  44. Thank you Svetlana

    you are really sweet girl. what you are presenting here of useful material reflects your sweetness, kind heart and sole.
    Thank you again love you and keep on.

    Cheers

    Jafar
    Saudi Arabia

    1. Thank you very much for your nice words, Jafar! You are too kind.

  45. We had a problem when we upgraded to 2013.
    When we sorted a table with a column containing the VLOOKUP formula
    =VLOOKUP(Analysis!D3,DEPT2,2,FALSE)
    The formula kept the reference to original cell that we were referring to
    So, if the above formula was located on row 3, and the sort moved it to row 41, it would still reference D3, rather than D41
    The table was located in the worksheet named "Analysis", so that worksheet reference was superfluous, and when we removed Analysis! from the formula, it adapted correctly to the new row following the sorting of the table.

    1. Hello Derek,

      Thank you very much for sharing this information. I've tested this behavior and got the same results.

      1. you're so pretty

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