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.

  1. You Need an Exact Match
  2. Lock the Table Reference
  3. A Column Has Been Inserted
  4. The Table has got Bigger
  5. VLOOKUP Cannot Look to its Left
  6. Your Table Contains Duplicates

You Need an Exact Match

The last argument of the VLOOKUP function, known as range_lookup, asks if you would like an approximate or an exact match.

In most cases people are looking for a particular product, order, employee or customer and therefore require an exact match. When looking for a unique value, FALSE should be entered for the range_lookup argument.

This argument is optional, but if left empty, the TRUE value is used. The TRUE value relies on your data being sorted in ascending order to work.

The image below shows a VLOOKUP with the range_lookup argument omitted, and the incorrect value being returned.
VLOOKUP function returned incorrect value because range_lookup argument is omitted


If looking for a unique value, enter FALSE for the last argument. The VLOOKUP above should be entered as =VLOOKUP(H3,B3:F11,2,FALSE).

Lock the Table Reference

Maybe you are looking to use multiple VLOOKUPs to return different information about a record. If you are planning to copy your VLOOKUP to multiple cells, you will need to lock your table.

The image below shows a VLOOKUP entered incorrectly. The wrong cell ranges are being referenced for the lookup_value and table array.
VLOOKUP entered incorrectly


The table that the VLOOKUP function uses to look for and return information from is known as the table_array. This will need to be referenced absolutely to copy your VLOOKUP.

Click on the references within the formula and press the F4 key on the keyboard to change the reference from relative to absolute. The formula should be entered as =VLOOKUP($H$3,$B$3:$F$11,4,FALSE).

In this example both the lookup_value and table_array references were made absolute. Typically it may be just the table_array that needs locking.

A Column Has Been Inserted

The column index number, or col_index_num, is used by the VLOOKUP function to enter what information to return about a record.

Because this is entered as an index number, it is not very durable. If a new column is inserted into the table, it could stop your VLOOKUP from working. The image below shows such a scenario.
VLOOKUP could stop working if you inserted new column in the lookup table

The quantity was in column 3, but after a new column was inserted it became column 4. However the VLOOKUP has not automatically updated.

Solution 1

One solution might be to protect the worksheet so that users cannot insert columns. If users will need to be able to do this, then it is not a viable solution.

Solution 2

Another option would be to insert the MATCH function into the col_index_num argument of VLOOKUP.

The MATCH function can be used to look for and return the required column number. This makes the col_index_num dynamic so inserted columns will no longer affect the VLOOKUP.

The formula below could be entered in this example to prevent the problem demonstrated above.
Use the MATCH function to return dynamic col_index_num

The Table has got Bigger

As more rows are added to the table, the VLOOKUP may need to be updated to ensure that these extra rows are included. The image below shows a VLOOKUP that does not check the entire table for the item of fruit.
VLOOKUP does not check newly added rows


Consider formatting the range as a table (Excel 2007+), or as a dynamic range name. These techniques will ensure that your VLOOKUP function will always be checking the entire table.

To format the range as a table, select the range of cells you want to use for the table_array and click Home > Format as Table and select a style from the gallery. Click the Design tab under Table Tools and change the table name in the box provided.

The VLOOKUP below shows a table named FruitList being used.
Use named range in VLOOKUP function

VLOOKUP Cannot Look to its Left

A limitation of the VLOOKUP function is that it cannot look to its left. It will look down the leftmost column of a table and return information from the right.


The solution to this involves not using VLOOKUP at all. Using a combination of the INDEX and MATCH functions of Excel is a common alternative to VLOOKUP. It is far more versatile.

The example below shows it being used to return information to the left of the column you are looking in.

Learn more about using INDEX and MATCH

Use INDEX and MATCH functions instead of VLOOKUP

Your Table Contains Duplicates

The VLOOKUP function can only return one record. It will return the first record that matches the value you looked for.

If your table contains duplicates then VLOOKUP will not be up to the task.

Solution 1

Should your list have duplicates? If not consider removing them. A quick way to do this is to select the table and click the Removes Duplicates button on the Data tab.

Check out the AbleBits Duplicate Remover for a more complete tool for handling duplicates in your Excel tables.

Solution 2

Ok, so your list should have duplicates. In this case a VLOOKUP is not what you need. A PivotTable would be perfect to select a value and list the results instead.

The table below is a list of orders. Let's say you want to return all the orders for a particular fruit.
Table with duplicated rows

A PivotTable has been used to enable a user to select a Fruit ID from the report filter and a list of all the orders appears.
Use PivotTable to group duplicate rows

Trouble Free VLOOKUPs

This article demonstrated a solution to the 6 most common reasons a VLOOKUP function is not working. Armed with this information you should enjoy a less troublesome future with this awesome Excel function.

About the Author

Alan Murray is an IT Trainer and the founder of Computergaga. He offers online training and the latest tips and tricks in Excel, Word, PowerPoint and Project.

You may also be interested in

Category: Excel Tips

Table of contents

168 responses to "6 Reasons Why Your VLOOKUP is Not Working"

  1. Sripathi Uday Kumar says:

    Thank you, it was helpful.

  2. Daniel says:

    The trapfall of VLOOKUP being not able to "look to right" wasn't known to me before. Very helpful!

  3. shahram says:

    Thank you, it was helpful.

  4. anil says:

    when we using vlookup it is show is same number in all colons

  5. Dan says:

    Sometimes the error may be because it has not calculated properly. If you have calculation set to manual rather than automatic, this can also cause an error when using vlookup or index match. Once the worksheet calculation is set to automatic, it works again (set it to automatic by going to the Formulas tab and then Calculation Operations and set to Automatic).

  6. Silvina says:

    Thanks a lot! very helpful :)

  7. Oskar says:

    thanks a lot for this information

  8. Brian says:

    Hopefully someone can help me. My vlookup is returning the correct values in my columns but it returns the same value until it comes across another non-zero value. For example the correct value of row 10 is 259 but it will return 259 for rows 11, 12, and 13. Row 14 will correctly show 864 but then rows 15 and 16 will too.

    Has anyone come across this?


  9. Alan Murray says:

    Hard to say why without seeing the spreadsheet but would expect there to be an error in the lookup value argument. I would check the reference.

  10. Ahmed says:

    thank you

  11. Andy says:

    Hi Don,

    Having calculation set from manual to automatic is very helpful. Save lots of time.

    Thank you very much.


  12. Tu N says:

    Thank you, thank you, thank you for the manual - automatic guide.

  13. K.Mahedner reddy says:

    can any help me put

    i had a sheet of name of the employs and their working hours, here the problem is in my sheet i had two names similar...(EX: mahesh as two time in the sheet )

    when i use vlookup in the table for my sheet i am not getting the second name

    how can i get the second name in the sheet........by using vlookup

  14. Alan Murray says:

    VLOOKUP won't be able to. It will only return the first name from the sheet.

    You will need more than 1 VLOOKUP. Or a macro would be needed for multiple occurrances of a name.

  15. Harshad says:

    Thanks for sharing. I had some hidden columns so my VLOOKUP was not working! Fixed it after readong Point 3. on your list.

  16. Kate says:

    i am doing a very simple lookup and I am getting the #n/a. Trying to pull a number into my master. The first column on both is my ref. the formula is =vlookup(A2,PLANC,2,false). Very very simple. I checked to make sure there was a match and there was and no duplicates were found. Help!

    • Jen says:

      I was having the same issue and just figured it out! (getting #N/A when I know there's an exact match)

      If you see the green triangle on the cell, hover over the yellow exclamation point "Number stored as text". From the dropdown select "Convert to Number" and it will fix it immediately. Good luck!

  17. Alan Murray says:

    Hard to say exactly without seeing the file. Check that there is definitely a match, so no spaces after the number. And also check the formatting of both the lookup_value and on the table_array to check they are the same.

  18. Mark W says:

    I am trying to write a VLookup formula to bring on hand data into a sheet that details sales volume. My formula is:: =VLOOKUP(A2,AA$2:AB$100,2,FALSE)

    Item B1007080SHF3MDO2BS***GG is not present on the target range; Item B1007080SHF3MDO2BSR**GG is there, with a required response value of 409.

    When I apply the VLookup to both fields, I get the value of 409 for BOTH, so the 'exact value' part of the formuala does not seem to be working. I have both lists sorted A-Z. It does not seem to matter if I format both columns as text or numbers, same issue (the full data set column does have values that are numbers only & alpha-numeric in both the search criteria and the target range).

  19. Mark W says:

    That might be. My ERP system only allows * as special characters so all my item number data exports have * in them. Do you know of a way to prevent this?

  20. Alan Murray says:

    You might be able to concatenate a string. Wrap the * in "" to indicate that you want to refer to that character literally and not as a wildcard.

    Otherwise you might be able to use the EXACT function with INDEX and MATCH instead of VLOOKUP. Information on this can be found below.


    I have not tested this, but think it should work. This function does what its name suggest and ensure the strings match.

  21. Belinda says:

    Watched the YouTube video and it was brilliant instruction!

    Saved me loads of time and frustration.

    Thanks so much.

  22. Gautam Lapsiya says:


    My table has duplicates in lookup cell, but the value against it is unique, what should I do if I want all the values populated?
    Column A Column B
    Banana USA
    Watermelon Brazil
    Banana Columbia

    Now if I want both USA and Columbia in how should I get it, as vlookup only gives USA?

  23. Belinda Nell says:

    Why does my vlookup give the same answer?

  24. Kathryn says:

    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.

  25. Zul says:

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

  26. Chris says:

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

  27. Amy Kassatly says:

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

  28. Ramprasad says:

    Why VLOOKUP with "TRUE" condition is not working on dates?

    • Alan Murray says:

      It should work with dates no problem. Ensure the table is sorted in earliest to latest by the date.

  29. Carlo says:

    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.

    • Alan Murray says:

      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.


  30. ABHIJEET says:


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

    • Alan Murray says:

      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.

  31. Jane says:

    Hi - VLOOKUP is working fine for me apart from only returning the first letter, i.e returning only 'J' instead of John. Any ideas?

    • Alan Murray says:

      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.

  32. Melanie says:

    number could also be stored as text

  33. DHurst says:

    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.


    • Alan Murray says:

      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.

  34. Yogananda says:

    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

  35. Mel says:

    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.

    • Alan says:

      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.


  36. farid says:

    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

  37. Noola says:

    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.

    • Alan says:

      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. Sergejs says:

    Alan,Thank you very much!!!!!

  39. j says:

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

    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%

  40. Subbhaiya says:

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

    • Alan Murray says:

      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.

  41. jahangir says:

    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

    • Alan Murray says:

      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.

  42. Dee says:

    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?

  43. Simon says:


    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.

    • Alan Murray says:

      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.


  44. Vinod Vaishnav says:

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


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

  45. Siyabonga Nxumalo says:

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

  46. m says:

    Thank you guys, great page!

  47. BERT says:

    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?

    • Alan says:

      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.

  48. Alan says:

    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.

  49. JimmyNZ says:

    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.

  50. bill says:

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

    • Alan Murray says:

      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.

  51. Judy says:

    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?

    • Alan Murray says:

      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.

  52. Mnaish Gupta says:

    Hello Alan,

    My vlookup values are too lengthy -
    eg values:

    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,


    • Alan Murray says:

      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.


  53. Gerry McArdle says:

    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

  54. Molly says:

    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?

    • Alan Murray says:

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

      • Cathy H says:

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

        • Alan Murray says:

          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


  55. Keith says:

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

    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.


    • Alan Murray says:

      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


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

      Hope this helps


  56. Annie says:

    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.


    • Alan Murray says:

      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.


  57. rhythm says:

    Indeed very helpful. Thanks.

  58. Madalin says:


    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!

    • Alan Murray says:

      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.

    • Madalin says:

      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,


      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!

  59. Archana says:

    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.

    • Alan Murray says:

      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.

  60. Abdallah says:

    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

  61. Saroj K Subedi says:

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

    • Alan Murray says:

      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.

  62. Sally says:


    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:


    Any ideas please?



    • Alan Murray says:

      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.

  63. Paola G says:

    You are awesome! Thank you.

  64. Alan Murray says:

    You're welcome Paola.

  65. Paul Wamalwa says:

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

  66. Julian H. says:

    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.

  67. Robert says:

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

  68. dick says:

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

  69. Gregory Werner says:

    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!

  70. Penn says:

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

  71. VS says:

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

  72. Ann says:

    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.

  73. sandeep saksham says:

    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.

  74. Harinath says:

    Hi Alan,
    Assigning Range_lookup to False solved my problem.
    Thank you.
    Best Regards,

  75. Bob says:

    I have a column of cells with lookup formulas all drawing data from the same table in another Excel worksheet.

    In fact, the cells have been copied down, so they’re identical, except for the relative referenced cell value I want them to lookup. In other words the look up table is locked into the copied formulas.

    All the cells with the vlookup formulas work fine, except one. It returns a 0.00. That is the correct format of the cell, but it will not grab the value from the lookup table.

    Any thoughts. I’ve tried everything I can think of.

  76. Alex Buchan says:

    I wondered why vlookup wasnt resolving - I mean it just stayed verbatim even after attempting to resolve - for example "=VLOOKUP(E3,Sheet1!$A$2:$B$2168,2,FALSE)". I discovered the cell was locked - unlocking it fixed the issue.

  77. Roger Gaspar says:

    I have a large Workbook comprising over 110 worksheets (37mb) and VLOOKUP is used extensively to mine the data into a 'Front Page'. In one part I use two columns to convert Time to a 'Tide Time' annotation;e.g. 10:33:00 is +4.33. In an array of 8 rows, the VLOOKUP correctly presents the correct 'Tide Time' in 7 out of 8 rows but one row falls short by one viz: 10:33:00 is displayed at +4.32. If I isolated the two columns into a separate workbook of course it works correctly but as part of the full workbook this error occurs. The column is simple values, not generated from other calculations but the columns are used by a large number of separate Named Cells. But I cannot identify why this one value is wrong.

  78. Roger Gaspar says:

    Message sent, thanks.

  79. Eric Watson says:

    My vlookup works if the cell CF2 does not have a formula in it.
    =VLOOKUP(CF2,Sheet2!A1:B921,2) my CF2 cell is =LEFT(M2,3).
    I want to look at a zip code, then take the 3 digit code (=LEFT(M2,3) and then vlookup that 3 digit value to find the shipping zone 1-9 in my range.

    • Hello Eric!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

      • Eric Watson says:

        I only need the first 3 digits of the zip code so I used =LEFT(M2,3) M is the customer zip code. My vlookup formula works if I manually enter a 3 digit code in cell CF2 =VLOOKUP(CF2,Sheet2!A1:B921,2,false) if the cell cf2 contains =LEFT(M2,3) then I get #N/A

    • Alan Murray says:

      Hi Eric,
      It seems as though the issue would be with the VLOOKUP needed a 0 for the last argument. If you are looking for a Zip Code you will want an exact match, and you are currently performing a range lookup.
      Failing that, the issue would be with the data and probably the Zip Codes because aside from the missing 0 or False on the end the formulas look great.

      • Eric Watson says:

        I only need the first 3 digits of the zip code so I used =LEFT(M2,3) M is the customer zip code. My vlookup formula works if I manually enter a 3 digit code in cell CF2 =VLOOKUP(CF2,Sheet2!A1:B921,2,false) if the cell cf2 contains =LEFT(M2,3) then I get #N/A

        • Alan Murray says:

          The other issue then may be formatting. LEFT is a text function so if the value you are looking for is numeric it won't work.
          Try wrapping the VALUE function around LEFT. So use =VALUE(LEFT(M2,3))

          • EW says:

            Brillant! I could not for the life of me figure this out. Thank you for your help!

            • Alan Murray says:

              You're welcome Eric.

              • Eric Watson says:

                In my formula I've run across another issue that is stumping me.
                =VLOOKUP(VALUE(LEFT(M51,3)),Sheet2!A50:B970,2,false) returns a value of 010 (Massachusetts 3 digit zip code) error code #N/A. Did not find value '10' in vlookup evaluation. I've tried every combination of formatting to get the leading zeros in a number. In my zip column M and on the vlookup page. Custom format 00000, 000, automatic number, plain text... Nothing is working for a 3 digit code that starts with a zero.

  80. sundaresan says:

    my vlookup function returns the cell value in the row above the answer cell...

    Dim area, pin As String
    area = InputBox("Enter Area", "Area Name")
    pin = Application.VLookup(Trim(area), Sheet1.Range("A1:B154800"), 2, True)
    ActiveCell.FormulaR1C1 = pin
    pl help me

  81. Jana says:

    My issue is that I work with duplicates that are needed.
    Pivot table is recommended, however, the result in my case is text not a value so pivot table is not really suitable.
    Is there any other function I could use?

  82. Earl says:

    My problem was that Excel was identifying A1 and A10 [which were formatted both as text] as the same value and therefore returning an #NA error. Some VLOOKUP values were good and some returned errors because of the issue.

    To resolve my problem I changed A10 to A9A. The issue then was cell values formatted as text returned seemingly random errors because it identified 0s as NULL values and therefore A1 and A10 as the same.

  83. Alyona says:

    Thank you so mush!
    my mistake was in 'VLOOKUP Cannot Look to its Left'

  84. Sanchia says:

    Thank you - my formula was driving me made and one little change has made it perfect. :)

  85. Siddharth Nanwani says:

    Thanks a lot, my problem got solved. Was unaware that 'VLOOKUP Cannot Look to its Left'

  86. Jasbir Singh says:

    Also, when you vlookup a cell and the lookup array is a text format - it shows error. For that convert the lookup array to general - then use text-to-columns feature to apply the general condition. This was really funny for me to find out.

  87. Willem says:

    I have used Vlookup to link destination sheet to master worksheet but when am updating the master sheet, the destination sheet is not updating

    • Alan Murray says:

      Check the entry is exactly the same as the one searched for. Also check the table array argument is the correct size.
      It is hard to say from here what the issue may be.

  88. A D says:

    Thanks a lot for reading me

    I wrote a Vlookup which take value from another sheet.

    my issue is that when I copy it to next line, it keeps taking previous line value.
    How could I fix this?

    Thanks a lot

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