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

  1. None of the above was the issue in my case. The issue was-
    The column where I was doing the VLOOKUP was formatted as “Text” changing it to “General” made it work.

  2. Bless you! This information is EXACTLY what I needed. I was so perplexed why my vlookup wasn't working and it turns out that the Index function with the match is what I needed.

    1. Excellent! Glad to hear that it worked out.

  3. My VLOOKUP is working fine if I unhide the table_Arrays referred but not when those are hidden

    1. when those are hidden, it displays #N/A. its quiet urgent if somebody can help me. It will be great help

  4. I omitted False in the equation so I got a mismatch. Big thanks!

    1. You're welcome Penn.

  5. I too got bitten by "VLOOKUP Cannot Look to its Left". I also could not get "LOOKUP" to work either for my dataset. The above solution is what finally did work for me, crazy that I can't do that in one command. Thanks so much!

    1. You're welcome Gregory. Thank you.

  6. make sure the calculation in the formula bar is on "automatic" rather than "manual"

    1. Nice comment dick.

  7. Great article! - don't forgot that the tabel must be sorted in order

    1. Yes, when performing approximate match VLOOKUPs.

  8. Another mistake is to check your reference list for duplicates. If you think the lookup value is only in there once, you may be mystified as to why the correct value is not being returned, but Vlookup is bringing back the first instance it finds.

    1. Thank you for your comment Julian.

  9. Thank you so much, you have saved me of worries. May you live long brother

    1. Thank you Paul.

  10. You're welcome Paola.

  11. You are awesome! Thank you.

  12. Hi

    I'm trying to do a basic V-look up and once I've entered it, and hit return on the first cell, all the cell displays is the formula, it doesn't pull any information. I've had this before but can't remember the fix.

    So instead of showing the result I'm looking for, my cell displays as follows:

    =VLOOKUP(C2,Sheet2!A:B,2,0)

    Any ideas please?

    Thanks

    Sally

    1. Hi Sally,
      It could be that the cell containing the formula is formatted as text. Check the drop down halfway on the Home tab.
      Or maybe formulas are set to be displayed. Check the Show Formulas button on the Formulas tab.
      Alan

  13. Dear Sir,
    I am working with vlookup function since a long time,but nowadays I got my results with some N/A results as unique value is common in both tables and resulting data also presents there but also results N/A for some rows.Please suggest..

    1. Sorry Saroj, I don't understand your question.

      The #N/A error will be present if it cannot find what you are looking for. It may be that the formatting of what you are looking for and where you are looking do not match.

  14. WOW , it's first time i know "VLOOKUP Cannot Look to its Left " this cause me lose more time . for years i don't know why Vlookup sometimes work and sometimes not :) .
    Thanks guys

    1. You're welcome Abdallah.

  15. Hi, My query is i want to use vlookup in book 1 sheet 1, with book 2 to pull out the data, so in book 2 it is not taking the formula. Please help.

    1. You should be able to set this up in the same way as if they were just in 2 different sheets.
      Make sure Book1 is open when you begin the VLOOKUP in Book2 and you should be able to select Book1 and then Sheet 1 to highlight the Table Array.
      The finished VLOOKUP will be as normal with the Table array showing [Book1.xlsx]Sheet1! at the start.

  16. Hello,

    I have an issue with the vlookup/match.

    For my case, the lookup value is not a value that I insert as input, but a formula. For example in A1=1 and A2=A1+1.
    The vlookup/match function is not behaving normal when I am looking for A2 in a table, ..sometimes it shows the N/A error and sometimes it works. If instead I write over in A2=2, then it works 100%.
    Because of the large number of data, this is not preferable for my case.
    Is there any solution for this issue?

    Thank you!
    Madalin

    1. Here is a sample of my data..it is possible that is not the optimum way to do it this way :)

      A1=2.9, A2=3, A3=3.1, A4-3.2, A5=3.3, B1=12.5, B2=18.2, B3=25.3, B4=33, B5=45,

      A7=30

      A9=MAX(IF(B1:B5<$A$7,B1:B5)) (=25.3)

      A11=INDEX($A$1:$A$5,MATCH(A9,B1:B5,0)) (=3.1)
      A12=A11+0.1 (=3.2)
      A13=A12+0.1 (=3.3)

      A15=INDEX(B1:B5,MATCH(A11,$A$1:$A$5,0)) (=25.3)
      A16=INDEX(B1:B5,MATCH(A12,$A$1:$A$5,0)) (=33)
      A17=INDEX(B1:B5,MATCH(A13,$A$1:$A$5,0)) (=N/A)

      If I change A13=3.3, then A17=45

      Thank you!
      Madalin

    2. Its always hard to say without seeing it but I would guess it to be an issue with formatting. The formatting of the lookup value and the first column of the table array must match.

  17. Indeed very helpful. Thanks.

  18. Hi Alan,

    I am new to lookup function so I tried as per your instructions (even copied the exact data as yours) but it still doesn't work. What did I do wrong. Please help.

    Thanks
    Annie

    1. Hi Annie,

      Without seeing what you have I cannot really help with this.

      It is typically one of the situations mentioned in this article, but to know exactly what is going on I would need to see the spreadsheet + formula.

      Alan

  19. Hi Alan,
    One work sheet, let say there are thousand of row, lookup value is the same format cell, table array no problem, column index no problem, range lookup is "0"), but the result is different at below:-

    7015028 CHUAN HUP SENG CHUAN HUP SENG
    7043640 SIN NAM HONG CAFÉ #N/A

    The only differences found is the lookup value cell. 7015028 has a caution mark at top left saying "The number in this cell is formatted as text or preceded by an apostrophe", where 7043640 is normal cell. Both cell format as GENERAL.

    Thanks.

    1. Hi Keith,

      Ok yes, if that column is the first of the table array and being used as the lookup value, then the format of both must match up.

      The one with the apostrophe is stored as text, the other is a number. Ignore the general along the top. One is text here and one is a number.

      If this is the one working, then convert the others in the column to text also.

      This could be done by selecting them as choosing Text from the menu that currently displays general.

      If this does not work, you could use this formula in an adjacent column

      =TEXT(A1,"0000000")

      Then copy and paste values the results over the current table array cells.

      Hope this helps

      Alan

  20. Hi Alan

    Great site and thanks so much for the time you take helping us all!

    My issue is I have brought in data from 2 different user's spreadsheets and the source data has leading zeros. We used a custom format o############ for both source and Col.A of Vlookup table - but the source data shows in the formula bar the leading zero whereas the vlookup col A does not show the leading zero in the formula bar and thinking this is why I keep getting N/A?

    1. Hi Molly, thank you.

      Yes the format of both columns must be the same for VLOOKUP. If the zeros are showing in the formula bar, it sounds like it is stored as text.

      You could use the VALUE function on that columns data to convert it from text representing a number to a number.

      Then try the VLOOKUP using that columns data. Good luck :)

      1. This is helpful. Can you tell me how to convert from special to general without losing the leading zero's?
        thank you!

        1. Hi Cathy H,

          You can try formatting the values as Text from that menu instead of General.

          Or use a formula in a different column such as

          =TEXT(A1, "00000")

          This example assumes your number is in A1 and you need 5 numbers including leading zeros.

          These results can then be copied and paste values over the current ones. This will change the format to text but keep the zeros.

          Hope that helps

          Alan

  21. I have a VLOOKUP problem. I am trying to put the text relating to health hazards into a risk assessment form. So if I select say H302 it puts the text in correctly but other values (they are formatted as text) such as H302+H312 (no spaces) it returns the value for H301 not H302+H312. What am I doing wrong

    1. Sorry should say H302 not H301

      1. Hi Gerry,

        Apologies because I am not completely sure what you mean. First thought is to check you have entered False or 0 in the last VLOOKUP argument, so it is not dependent upon order.

        I hope you solve the mystery.

        Alan

        1. Alan,

          Thank you, I had omitted the FALSE entry. Everything is fine now.

          Gerry

          1. Great to hear. Thanks for letting me know Gerry.

  22. Hello Alan,

    My vlookup values are too lengthy -
    eg values:
    1) MCDK904745/MCDK904746;ZCL_IM_CRM_ORDERADM_H_BADI IF_EX_CRM_ORDERADM_H_BADI~CRM_ORDERADM_H_MERGE
    2) MCDK904884/MCDK904885;ZCL_IM_CRM_ORDERADM_I_BADI IF_EX_CRM_ORDERADM_I_BADI~CRM_ORDERADM_I_MERGE

    Thus, when the file is doing a Vlookup for smaller values, they are present, but pulling #NA for these big values. These values are the result of a report so I cannot change these values. But based on these values, I will have to pull data from other columns.

    Please advice; what exactly am i missing here and how to tackle this issue

    Thanks a lot in advance for all your help

    Best regards,

    Manish

    1. Hi Manish,

      Th eproblem is that it does not like the Tile "~" in the text. You may need to find a way of removing or replacing this for the purpose of the lookup.

      Don't need to remove it from the cell if important. Just temporarily for the lookup. Could use the SUBSTITUTE function in the VLOOKUP.

      Alan

  23. My vlookup is only working for the top half of my spreadsheet and then stops working on any cell past row 270. My vlookup table is a named range, first column in order by dates, returning column is to the left of the reference column and formatting matches and is not text. Anyone have a clue how to fix it?

    1. Hi Judy, If the VLOOKUP does not look past row 270 I would check out the named range as that would seem the problem. You can edit this named range from the Formulas tab and the Name Manager.

      The formatting of the lookup value and first column of the named range must be matching. And the col index num (returning column) must be to the right of the reference column.

      The problem in this article on VLOOKUP cannot look to the left can help with this.

  24. another tip: if you're looking up numbers, make sure they are not 'numbers formatted as text.' They'll break the formula.

    1. Thanks Bill.

      Yes to add to that... the formatting of the lookup value and the first column of the table array must match. Whether they both be numbers, or both be numbers formatted as text, they must be the same.

  25. I have tried all the solutions the article mentioned, still failed then I tried this, always start the looking table with the value you are looking for in the first column e.g. VLOOKUP(sheet1!A2, sheet2!$C$2:$E$84, 1, FALSE). That is you are looking for value in column C in sheet2 that matches value in column A in sheet1. It is magic! to solve a problem of VLOOKUP that happens when we copy data from different data sources.

    1. Thanks for the tip JimmyNZ.

  26. Yes VLOOKUP will only return the first match. You can use a PivotTable to perform actions like show all invoice # for a particular aircraft.

    Otherwise if you wish to populate a bunch of cells with this info then you are looking for a macro probably.

  27. Hi! I worked in airline company and one task I have is to matched the invoice # to a particular aircraft
    I used vlookup but it will only return the first match. Sometime an aircraft comes multiple times in a month. How do I solve this?

    1. Yes VLOOKUP will only return the first match. You can use a PivotTable to perform actions like show all invoice # for a particular aircraft.
      Otherwise if you wish to populate a bunch of cells with this info then you are looking for a macro probably.

  28. Thank you guys, great page!

  29. Thank you very much!! It worked. I forgot to lock my table.

  30. columns values same but rows values change how can use vlookup formula

    E.g.

    Same Change Value
    Name AAAA BBBB CCCC DDDD
    A 15 12 16 22
    B 45 26 33 99
    C 88 33 78 98
    B 22 34 56 37

  31. Hi,

    I’m trying to use VLOOKUP to look up values with a combination of FS500000, 1000000, F500000. The VLOOKUP is finding combination FS500000 and F500000, however fields with 1000000 are showing the #NA error.
    The cells are formatted as general (also tried switching to text), i've removed "hidden" fields and inserted TRIM within the formula, but still no luck.

    It’s just a simple VLOOKUP formula of =VLOOKUP(A9,Sheet2!A1:K10000,6,FALSE)

    Any help would be appreciated.
    Thanks.

    1. Hi Simon,

      I did suspect this would be due to the formatting. General is not adequate, Excel will still view 1000000 as a number.

      Formatting as text is good. Just ensure the lookup value and the first column of the table array are the same format. So both need to be text.

      Hope it works out.

      Alan

  32. I keep receiving a #REF! error when using a VLOOKUP between two workbooks on a network share.

    If I have the VLOOKUP workbook open (showing the #REF! errors) and then I open up the workbook that contains the table array - then the #REF! errors disappear and the cells contain the correct data. It seems as if I must have the source workbook open in order for the link between the two to function correctly. Do you have any suggestions that I could try?

  33. i make a table using vlookup but its not working properly.
    in same row some times show result okay some times not okay. how to resolve. pls help

    1. I can't really explain without more information. Maybe check the typing of the entries.

      You say it works for some and not others. Sounds like a mis-type on those entries not working. Or maybe the second reason above.

  34. I am unable to do vlookup between 2 google sheets. 2 sheets are not getting linked.

    1. I do not use Google Sheets but am under the impression it works in a similar way. I would re-check the VLOOKUP for potential mistake.

  35. can someone please help urgently. i am trying to find the status for the sales but it is only working for the price. pleasee help!!! i don't know what to do!!

    USE FUNCTION
    Vlookup IF
    Brand Products Quantity Price Sales Status Tax
    Samsung Projector 15 $1,500.00 $22,500.00 Poor
    HP L Printer 20 $1,200.00 $24,000.00 Poor
    APPLE Mac/Air 5 $2,200.00 $11,000.00 Poor
    Samsung Laptop 10 $1,600.00 $16,000.00 Poor
    HP Camera 25 $800.00 $20,000.00 Poor
    Samsung Galaxy tab 12 $1,200.00 $14,400.00 Poor
    ACER Netbook 18 $900.00 $16,200.00 Poor
    DELL DUO/Laptop 13 $2,500.00 $32,500.00 Poor
    APPLE Ipad 4 16 $1,400.00 $22,400.00 Poor
    TOTAL =>
    STATUS more than $30,000.00 Excellent
    more than $25,000.00 Good
    more than $20,000.00 Average
    more than $15,000.00 Trial
    less than $15,000.00 Poor

    TAX more than $30,000.00 25% Of SALES
    more than $25,000.00 20%
    more than $20,000.00 15%
    more than $15,000.00 10%
    less than $15,000.00 5%

  36. Alan,Thank you very much!!!!!

  37. Another issue can be "unknown characters" instead of spaces being used. I was looking at a file with 2 sets of data that looked the same, but they were extracted by different means from a database, and on one set of data, spaces were not spaces but some other character. Found it by checking if 2 items that look identical were actually identical (they weren't, according to Excel), and the only possible characters that could be different were those I couldn't see, i.e. spaces.

    1. Yes sometimes data needs to be cleansed after importing from a database before formulas such as VLOOKUP can be run.

      Text functions such as SUBSTITUTE and TRIM are great for this sort of tasks. The Find & Replace and Power Query tools are also brilliant for cleansing data.

  38. i want to use vlookup function my data have duplicate value but i don't want to remove it ,so i want use vlookup function but it gave me the first value of duplicate value i want to find the second value that belong to duplicate please help me

    1. Hi Farid,

      Check out this video - https://youtu.be/QAZ3L6xbNJc

      It shows how to use VLOOKUP to return the last match if there are duplicates. You can adapt this easily for the 2nd match though.

      Alan

  39. Hi Alan
    I'm doing a VLOOKUP which I do every month to put dates next to asset ID numbers. My LOOKUP results are exactly the same for every ID number when i copy down my formula. So the results for the first row are correct but the rest are wrong as they're the same as the first.

    1. Hi Mel,

      It sounds like your lookup_value is absolute so is not changing when you copy the formula.

      You might need to remove the dollar signs from the first part of your VLOOKUP.

      Alan

  40. While selecting rows and col in vlookup formula (the area where to search) the row col reference (eg. 8RX4C) is not displaying, It is getting difficult to count manually for larger tables.

    Please help me on this issue

    Thanks in advance

    1. I'm struggling a little with the question but I think you are asking about entering the col index num when using a big table.

      In such an example the MATCH function can be used to look along the header row and locate the column number for you. This is done in a similar way to the 5th example of this tutorial and in the link below.

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

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

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

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

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

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

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

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

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

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

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

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