Why Is My Vlookup Not Working?

Reasons Why Your Formula Broke

The Vlookup formula in Excel is a powerful tool to extract the data you are looking for from large datasets. However, there are times when it does not behave as you intend. In most cases, this is due to something being off within the dataset you are parsing.

How To Troubleshoot

The first step to troubleshooting is to confirm that your syntax is correct. Ensure you have specified a lookup value, range, TRUE or FALSE value, and an index. If you have confirmed these are working, start to look at your reference data (data in the range) and source data (your lookup values). Be sure that the data is present and formatted correctly.

Once you have confirmed this, take a look at some common reasons your Vlookup is not working and how to fix them.

What happened? You copied a Vlookup from another workbook and it’s not working.

The Vlookup formula is highly dependent on the dataset it is referencing. As such, it’s nigh impossible for it to work the same way it did across different workbooks without modification. If you highlight a cell, copy it, and paste it into another spreadsheet, the formula will still be pointing to the original spreadsheet.

This of course means it won’t be looking at the data in the new spreadsheet which will result in a bunch of &quo;N/A#&quo;’s being displayed.

How do I fix it? Click the cell and copy the contents of the formula bar

To capture the text of the formula, you will need to first click within the cell that contains the Vlookup. From there, head over to the formula bar and highlight the entire formula that is displayed. Copy that and paste it into your new workbook and the formula will be pointing in the right direction.

What happened? The numbers you are trying to locate are not being found.

Why is this happening? Numbers in your reference data and source data are not in the same format.

Excel will format longer numbers in scientific notation. So, if you have a series of numbers that are not in scientific notation, the Vlookup won’t find a match.

How do I fix it? Change the cells in scientific notation to standard number values

The easiest way to do this is to use custom formatting on the cells. This feature is accessed via the ribbon located near the top of the spreadsheet in the Number area.

Select the More Number Formats option.

Within the number tab, select the custom option. From there, select zero from the menu on the right. Click OK and scientific notation should disappear from your targeted cells.

Another way to remove this formatting is to append an apostrophe to the data in the cells you are going to format. This is accomplished through concatenation (a fancy way of saying joining things together).

Here is what your formula should look like.

="'"&A1

In a new column, type an equals sign. Then enter a quotation mark, followed by an apostrophe and finally another quotation mark. Next, enter an ampersand and either click the cell that has the number in scientific notation or type it into the formula.

Press the enter key and your number will be out of scientific notation.

What happened? Vlookup is not finding the words or characters I am looking for!

If your formula's syntax is correct and you are certain the data you are looking for is in your reference data, you should find a match. In the event you don't, it's time to start looking for formatting issues.

Why is this happening (theory 1)? The words or characters are not EXACTLY the same in source and reference data.

In your Vlookup, if you have specified FALSE as a parameter, your formula will be looking for an exact match. If the data in your reference data is even one character off, it will return an #N/A.

How do I fix it? Remove any whitespace or erroneous characters.

First, try to remove all the whitespace in both the reference and source data. To do this highlight the column containing the data you are trying to match. If you are on Windows press the CTRL key and F key. For Mac users, press Command and F.

You will be presented with the menu below. Select the replace tab. Now, in the find field, press the spacebar once. Leave the replace column blank.

You are telling Excel to locate any single occurrence of whitespace and erase it. To be certain you have gotten rid of all whitespace, you can repeat the same exercise by clicking the spacebar twice in the find field.

This will remove double whitespaces as well. If you are still encountering errors, look for erroneous characters in your data. At times, when you export Excel or csv files from an application there will be quotation marks, apostrophes and other characters appended to some cells.

Strip these out using the control, find, and replace method outlined for whitespace above.

Why is this happening (theory 2)? The data is there but the reference data structure has changed.

If you have inserted a column or deleted one, Excel won’t dynamically update your Vlookup. If your Vlookup was working before but isn’t now, this is probably the culprit. Take a look at the columns your formula is referencing and if it has moved, update the formula accordingly.

What happened? You got a #SPILL error when running your Vlookup.

Why is this happening? You failed to specify a value for the first parameter

Excel needs to know which value you are looking for. If there is not one listed, the formula won't be able to look through the reference data for a match.

This error is probably one of the simpler to solve, at least with respect to the Vlookup formula. Excel is unable to return your results because you have not specified a lookup value. Simply add the lookup value and the problem will be resolved.

I tried everything and it still doesn't work!

If none of the above tips were not able to help, always check your data! In addition, Excel itself may be the issue. If you have been putting off an update, try it and see if it resolves the issue.

Your computer may also be an issue. Ensure you have enough CPU power and RAM to handle your workflow. If your computer freezes in the middle of your analysis, it may not have copied all the data over you thought it did.

Are you still in need of help? Check out my other article on vlookup syntax and use.

More Articles...