Solutions to Three Common Problems when Using VLOOKUP()

Problem #2:   We know our data matches, but VLOOKUP() is returning #N/A.

Solution #2:   The problem is almost always that the keys are a mixture of numeric values and text values within the cells and one of the key columns is formatted as GENERAL and the other is formatted as TEXT.  Here the left table’s key is formatted as General and the right’s key is formatted as Text.

The first solution that will normally occur to us is “I’ll just format the General column as Text” (or vice-versa).  So we highlight one of the columns and hit Ctrl+F1 (or Home | Format | Format Cells (2007, 2010) or Format | Cells… (2003 & below)) and change the format and …. What!? It doesn’t fix the problem.  Changing a cell’s format doesn’t “take” until you edit the cell.  If we have more than just a few rows, we are not going to want to plink back and forth hitting F2 then ENTER a couple hundred times.  We have two other options. One is to use Excel’s built-in error correct if it has flagged this for us.  In the screenshot below it has done so.  We can then highlight all of the cells in that column and pick “Convert to Number” from the error correction popup menu.

 If we do not have the error correction option or if we simply prefer this method, we can use the Text to Columns tool instead.  To use that we highlight the column whose format we wish to change and then from the menu (using Excel 2007 here) select Data | Text to Columns… and we see a wizard that looks like this:

We can just leave the DELIMITED option in place and click Next > and then make sure that whatever delimiter we have checked does not actually occur in your column.  Normally just sticking with TAB will work fine since it very rarely occurs in a cell.  Then we click Next > again and on Step 3 we pick the format we need General or Text and click Finish.

 Providing we correctly remembered what format we needed, we should be rewarded with the VLOOKUP() function working properly.

Alternate Solution – if we’re feeling adventurous, we can do the type conversion in-formula by coercing the data types.  If the cells that go into the first VLOOKUP argument are formatted as TEXT and the keys that are in the range that are in the second argument are formatted as GENERAL then something like:

• =VLOOKUP(IF(ISERR(Key2+0),Key2,Key2+0),Sheet1!Data,2,FALSE) 

will coerce the first argument to GENERAL format whenever the data is numeric in nature. 

 If it’s the data that’s in the key field of the 2^nd argument’s range that is TEXT format and our first argument’s cell is GENERAL format then concatenating an empty string:

•  =VLOOKUP(Key2&””,Sheet1!Data,2,FALSE)

 will coerce the numbers in the first argument to all text.  [Note: in these examples the second argument is a named range.]

Next most likely solution – If the problem is not a mixture of text and numbers, then the next most common cause is extra spaces on one of the columns.  One way we can quickly check this is by finding a cell that has returned #N/A when it shouldn’t and clicking on the key cell and hitting F2 and inspecting the cell’s contents in the formula bar.  If there are no leading or trailing spaces there, look at the corresponding key cell in the lookup range the same way.  If we confirm that the problem is indeed being caused by leading or trailing spaces and if our data values have no spaces in the middle of them (our data reads “ExcelIsReallyFun     ” or “    ExcelIsReallyRun”; and not “   Excel Is Really Fun”) then the simplest solution would be to just select the cells with too many spaces and hit Ctrl+H (Replace) and enter a space for the Find What argument and nothing at all for the Replace With argument.

If there are spaces within the cells’ values, one solution is to use the TRIM() function.  If the extra spaces are in the first argument’s cell, the solution is simple; we just insert TRIM in that first argument:

• =VLOOKUP(TRIM(KEY),Sheet1!Data, 2, FALSE)

 If the extra spaces are in the lookup range then we can insert a helper column and use TRIM() in the helper column to eliminate leading and trailing spaces.  We can then leave the helper column and change our VLOOKUP() to match on the helper column, or we can do a Copy and Paste Special… | Values over the top on the helper and then overwrite or delete the original column with the trimmed values.  (The easiest way to access Paste Special regardless of version is to right-click once you have copied the cells.)

 If our data is always the same length after leading/trailing spaces are removed then we could also use the Fixed Width option on theText to Columns tool described above and split off any leading and trailing spaces and choose SKIP for the columns of extra spaces.

Go to Main Page/Problem #1

Go to Problem #3:

Also read Learn to Use INDEX MATCH MATCH