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.

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

Solution

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

Solution

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

Solution

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.

Solution

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.

217 comments

  1. For the most part my Vlookup returns the correct values, but here and there I get #N/A even though the values exists and are in the correct format, what I would usually do is start a new vlookup formula on that cell and copy paste it on the remaining cells and it works until I encounter another set of #N/A, I just find it counter productive

  2. Can't believe I didn't know VLOOKUP can't look to its left. Thank you! I wish I'd found your article earlier but at least now I know

  3. i am using vlookup on 2 tables payroll and salaried staff.
    salaried staff has data going from payroll to it no issues, but when it comes to going the other way no go except from on one employee.
    salaried staff to payroll not working on both except from the one employee
    1. contract hours
    2. overtime

    payrol to salaried staff
    1. base hours (yes)
    2.total days worked (yes)
    3. Secondment (no)
    4.Holdiays (no)

    i have checked the name manually and copied ans pasted, named the table, i have not edited the table, tried coping the formula from the cell wear it is working. all my data is right of the column wqith the name in it as the sreach criteria, there are no duplicate names in the same table.

    • Sorry, I do not fully understand the task. To understand what you want to do, give an example of the source data and the desired result.

  4. iii. In total your formula will look like very similar to this: =IF(ISNA(VLOOKUP(H2, ‘Quarter 4 MRNs’!$B2:$B$45, 1, FALSE)), “Not Duplicate”,””)
    iv. Type in =IF(ISNA(VLOOKUP( then select the cell H2
    v. Continue the formula by inserting a comma, then click on the Quarter 4 MRNs tab select B2 and scroll down to include all of your previous weeks MRNs.
    vi. To complete the formula, type in the following , 1, FALSE)), “Not Duplicate”,””).
    vii. To save time click on that formula value and adjust the following B2:B45 to $B$2: $B$45. Now instead of retyping this formula for each MRN listed, you can scroll to the green box, click on it, and scroll down to the end of your list.
    viii. Hit Return.
    ix. Your column will list the value “Not Duplicate” when the patient’s MRN is not found in any previous weeks; if the cell is left blank you can eliminate that patient from the list because their information will already have been provided to the screeners previously.

    So this formula works, I delete MRN's that show up again in a 6 month period of time. The change is now I have to pull the data using a new tool, long story short, it exports data originally as a CSV file, I then save it as an excel file, and move it to my "Eligibility List" excel file. Since then, the formula will not recognize the Quarter 4 MRN's tab, even though it is now a part of that document. Help?

    • Hi! If you delete a worksheet and then create a new worksheet with the same name, your formulas will not work. You get #REF! error. Also, the references in your formulas will change if you delete the rows or columns they refer to.

      • if you are forced to delete a workbook and remake it. Highlight the column(s) then press ctrl F & use the replace function. search for #REF! and replace ALL with '[workbookname.xlsm]worksheetname'!

  5. My vlookup is not working - the lookup data looks good but there must be something about the data/cell that is causing this issue. If I copy and paste into the lookup cell from the lookup table the data is brought through ok. I have run clean and also pasted data into notepad and reinserted but still get this issue

    • I can't see your data and your problem. But if VLOOKUP doesn't work, then the values are different by a space or some unprintable character. Numbers can differ by a decimal digit.

  6. My vlookup is returning the same 0 value although I have checked my formula, it is correct.
    =VLOOKUP($M$2,'4-5 update'!$M$2:$T$284,8,FALSE)

  7. My vlookup formula is returning the same data for all my rows/column regardless of the unique identifier. Not sure why this is happenings because every other time I have used vlookup, it was fine. I triple checked the formula and everything is correct. Please advise

    • You checked the formula, but you didn't write it. How can I help if I can't see it? Give me an example of the data you are searching for and getting it out.

  8. Hi guys, I am using a VLookup to award an amount when a certain percentage is hit. The Vlookup cell I am using is a copied cell. It will not find the data when its a copied cell. Is there away around this?

    Thanks

    • Hi!
      The information you provided is not enough to understand your case and give you any advice, sorry. Describe your task in more detail.

  9. And another. Your lookup table reference is larger than your table. As my lookup table gets larger, daily, I am forced to increase my lookup reference size. This created an issue. It would be nice to not have to manually change the lookup reference but......

  10. Let me add a seventh reason. Your excel calculation is set to Manual rather than automatic so if you go in the search bar and type in "Calculation" you will be able to see what your setting is for that excel workbook.

    • You just saved my life!

      • Thanks a lot for this!!!!

  11. When i try doing a VLOOKUP, many times when I select the table array it doesn't pick up the excel sheet.

    Is there a certain format the text has to be on both excel sheets?

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