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

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

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

  3. You're a lifesaver, thanks!

  4. 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."

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

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

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

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

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

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

  11. Hello!
    I am doing match. If we put =iferror...;" ") we get blank cells instead of #N/A. That is ok, BUT in that case I want to keep my original number. Is it possible?
    Thank you for the answer!

  12. Convert to number, convert to number, convert to number..... I always forget to do that :P

  13. I have 2 sheets with below data and i want to copy data(value) from sheet 2 to sheet1

    sheet1:
    id value
    1
    3
    2

    sheet2:
    1 A
    2 BB
    3 C

    vlookup used: =VLOOKUP(Sheet2!A2,Sheet2!A2:B4,2,0)

    Result:
    1 A
    3 BB
    2 C

    Expected:
    1 A
    3 C
    2 BB

    I am looking for exact match for the value being searched. However, it is giving me values as per sequence.

    Any help would be appreciated.

  14. AMAZING WORK THANK YOU VERY MUCH

  15. Hi All,

    There is a problem when I try to compare two excel sheets using vlookup and if both the columns are empty which are compared. It returns an error. what to do in this condition???
    Best Regards,
    Faraz

  16. Very nice post. Solve my problem.

  17. I have pay grade coded as 4-X1, 4-X2 representing salary 50,000 and 65,000 in two columns.
    I get #N/A error using vlookup function when looking for any pay grade.. for example 4-X1

    Why do I get this error? please assist!!

  18. I need some VLOOKUP formula help

  19. I have been struggling with the following issue and all the recommended fixes have not worked.
    I am using vlookup to pull data from four other sheets. I need to be able to use the results in cell to trigger the next search, however what happens is that the next vlookup looks up the values in the formuala, not the result.
    I.E.
    =VLOOKUP(E3, DATA!A2:Z480, 4, FALSE)

    and in E3 I have:

    =VLOOKUP(A2, OTHER! A1:Z400, 2, FALSE)

    Instead of looking for the displayed value in E3, it goes chasing down the previous VLOOKUP and tries to find A2, and if THAT was another formula, it just keeps going.

    I have changed all cell formats to general, number or anything but text and have turned off 'display formulas'.

    Anyone got any suggestions?

    Thank you.

  20. Hi

    How can i change format of 112-65-4171
    to
    812-48-7524

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

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

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

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

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

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

  27. very gud I enjoy

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

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

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

  31. nice tricks

  32. Hello,

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

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

  34. Thanks !!

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

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

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

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

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

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

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

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

  43. Thanks Svetlana

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

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

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

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

    thanks

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

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

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

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