Comments on: Excel VLOOKUP errors: #N/A, #VALUE, #NAME

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

  1. I have a tale on excel file that contain vlookup function when i open that excel file on other network computer its convert to range what is the problam with that anyone please help me

  2. Hi
    how can I use some Vlookups in a cell;means I want to Vlookup some tables and show one of answer(or even first on)in a cell?
    The symbol &(between Vlookups) didn't work here!:-(

    thanks for Help
    AMS

  3. while using vlookup for row and column detail not showing... pls help

  4. This is driving me nuts. The VLOOKUP worked on my 2015 data. but when I replicated it with 2016, using the same formulas, the lookup fails retunring #N/A...

    Col B is a list of names, such as
    Name
    Jaclyn Smith - Sr
    Yvette Burcescu - Jr
    Mychael Vernon - Fr
    Kiara Adams - Jr

    the lookup array is
    Name_(11) Name Section Award
    Aida Rossi Aida Rossi - Valhalla All Conference Honorable Mention
    Alexandra A Alexandra Antony - Blind Brook All Conference Honorable Mention
    Amanda Marj Amanda Marji - Yonkers All Conference Honorable Mention
    Anjali Seba Anjali Sebastian - Clarkstown North All Section

    Formula is =IFERROR(VLOOKUP(LEFT(B42,11),Names_Awards_2016,3,FALSE),"") returns an #N/A error, however, if I replace the LEFT(B42,11) with the actual texts string ex:"Jaclyn Smit", the formula appears to work.

    Help.

  5. Hi,

    I am trying to create a vlookup and it is not getting executed. The below formula is shown in the column instead of the result

    =VLOOKUP(A3,'[11i Extract 300000.xlsx]Sheet1'!$A$1:$A$3995,1,false)

  6. Hello:
    We have an unusual problem with VLOOKUP. First, the format we use:

    =VLOOKUP(A6,dec2016register,7,FALSE)

    This command is in the 2017 workbook and is retrieving a number from another separate 2016 workbook.

    Where dec2016register is the named array for a worksheet in one workbook and has a value we want to export to a worksheet within another, separate workbook. Both workbooks are open, both reside in the same folder. We defined the array, there is no data conflict with the Lookup Value in either worksheet, and the exact same command (with different arrays) works perfectly in one other application involving two separate workbooks. BUT with this one particular application, we keep getting a #NAME? error. Why? What are we doing wrong???

  7. very gud I enjoy

  8. Hi,

    I have two worksheets, one with the list of names with related information in each row, and the other is for the query. I provide the name in one of the cells of my query sheet, and I want to use the MATCH function to get the row number (from the names worksheet) of the specified name.

    I am trying to use the following:
    =MATCH($B$3,$B$2!A1:A10, 0)

    Thanks in advance.

    -Ravi
    Where I specify the required name to be queried in the B3 cell of the query sheet, and B2 cell has the name of the names sheet.
    In short, I want to use variables to specify the lookup-value and lookup-range. How do I go?

  9. Thank you so much, Svetlana, for the troubleshooting tips! Without them, I was struggling for hours trying to get VLOOKUP to work across two excel workbooks. What finally worked for me were sorting the lookup column in ascending order, and bringing that column to lie AFTER the starting column index of the Lookup table (as you suggested above). Many thanks, especially as I did not find such trouble shooting steps in Excel also.

  10. Hi,

    I have two excel sheets one contains insurance policies issued by the insurance company through insurance broker along with the premium amount and the commission amount and another excel sheets which is provided by the insurance broker which has policies issued on behalf of the insurance company it has premium amount and the commission amount. Now my question is, i want to match both excel sheets by using vlookup. I matched large volume of data. But, when the same policy number is appears twice or thrice, vlookup showing the policy which comes first and ignores the remaining two. I want to know about the policy which are appearing twice or thrice with relevant premium amount. I read the how to get all duplicate values in the vlookup range. However, it seems like it is small volume of data. How do i it for the large volume of data?

  11. nice tricks

  12. Hello,

    I am facing problem during using of VLOOKUP formula , Row & column number not showing.. anyone can give your advice?

  13. Hi experts,

    is it possible to look for a value which is the formula? I tried vlookup but not working, when I put look up value manually then it`s fine.

    thanks for help!
    tom

  14. Thanks !!

  15. Hello, I've been having some trouble using this formula.

    I've got this code that I have to divide in three parts which I've resolved using LEFT,MID and RIGHT functions. Each part has to be reidentified with some data, using VLOOKUP I have identified the first and last part (which were letters), and I tried using this function for the middle part (which are numbers) but it returns #N/A. I tried changing the cell format from text to numbers but it's the same result

    Code: q-15-PAL
    =MID(B2,3,2) = 15
    =VLOOKUP(E2,M2:N38,2) = Palanca (this is what's supposed to appear)

    can somebody help me?

  16. A problem with…
    "Use VLOOKUP and INDIRECT to dynamically pull data from different sheets"
    =VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)reference formula

    Since my reference cell Y3 needs to be a number and my Named Ranges can start with a number (LIST15, LIST18, LIST20) I cannot get this formula to work.

    =VLOOKUP($AE3,INDIRECT($Y3&"_LIST"),2,0)

    I even tried to move the underscore to the right side to match the way it is in the Named Ranges and still don’t work.

    =VLOOKUP($AE3,INDIRECT($Y3&"LIST_"),2,0)

    I get a #REF! Error in both cases. Can anyone help?

  17. I too facing the common error like #N/A, even after i convert the look up range in number format. kindly suggest. any other alter.

  18. THANK YOU!!!! You just saved me. TRIM...who knew!

  19. Hi,
    My V-lookup is working, but the formula will not copying down the full column of data. For example, 100 cells of data, first cell will v-look up correctly, but the rest of the column can't be completed without repeating the v-lookup an additonal 99 times, in each cell. Does that make sense?
    Any thoughts on how to fix?
    Thank you.

  20. Had a similar issue today with a vlookup. I tried the suggestions above but nothing worked. I had to insert a value() into the field lookup value in order to conduct the search. Oddly, the field being looked up is formatted properly as well as the range it is looking at. An example:

    =VLOOKUP(VALUE(A1691),DSI!$B$2:$D$769,3,FALSE)

    1. Hello, Brian,

      To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

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

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

  23. Thanks Svetlana

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

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

  26. 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 (( ? )))

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

    thanks

  28. 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!!

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

  30. 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)

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

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

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

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

  35. Hello,

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

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

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

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

  39. hi,

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

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

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

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

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

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

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

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

  47. 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?

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

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

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

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