Fix Excel VLOOKUP Error Problem Video Steps

Sometimes a VLOOKUP formula returns an #N/A error, even though you can see the value you’re looking for in the lookup table. The most common cause for this is a text “number” in one place, and a real number in the other place. See how you can fix that VLOOKUP problem.

Text Numbers and Real Numbers

Even if two numbers on the worksheet look the same, Excel might see one as text, and the other as a number. And in Excel, those two things are not equal.

For example, the lookup table may contain ‘123 (text), and the value to look up is 123 (a real number). The video below shows how you can fix the problem, if that’s the situation in your workbook.

Note: If your workbook has a different type of VLOOKUP problem, take a look at other solutions and examples on the VLOOKUP Examples page on my Contextures site.

Video: Fix VLOOKUP Text vs. Number Problem

This video shows the steps for fixing a VLOOKUP problem when the lookup table has text values, and the lookup value is a real number.

The full transcript is below the video, and you can get the Excel sample workbook on the VLOOKUP Examples page on my Contextures site.

Video Timeline

00:00 VLOOKUP Error Problem
00:44 Values Not Equal
01:37 Fix Values Not Equal
02:07 Change VLOOKUP Formula

Transcript: Fix VLOOKUP Text vs. Number Problem

Here is the full transcript for the Numbers and Text Troubleshooting video shown above.

—————————-

Usually the VLOOKUP formula in Excel works very well. We could enter product code, and the VLOOKUP formula would return the product name or price for that product code.

But in this example, we’ve typed a code here.

We can see that code in the table but the VLOOKUP is returning an N/A error.

In the formula bar, you can see that VLOOKUP formula.

We’re referring to the value in B8
looking up in the code column
asking for the value in the second column

So it should be giving us the product but it isn’t. So we’ll do a bit of troubleshooting, to see what the problem is, and how we can solve it

Values Not Equal

Sometimes the problem is, things that look the same on the worksheet, aren’t really a match.

We’ll see if what we typed in B8 is really a match for what’s in B2.

In this cell, I’m going to just do a simple test.

I’ll click here
type an equal sign
then click on B2
another equal sign
and B8

It’s coming back FALSE, so there’s something different about these values

If I click on B2, I can see an apostrophe at the start of that number.
- So it’s been entered as text. Maybe this was a download from another file, and it’s stored as text there.
If I click on B8, there’s no apostrophe.
- So this is a number, and the number is not equal to the text, which would have a value of 0.

So how can we fix this?

Fix Values Not Equal

If we select all these cells, one way to fix it, would be to change all of these to numbers, so they match the values we’re going to type in here as numbers.

To do that, I can:

Go to the Ribbon
On the Data tab, click Text to Columns
When this comes up, just click Finish

That’s automatically changed all of these to numbers, and you can see that our VLOOKUP is working correctly now.

So it’s showing me that 123 is the product called Paper.

Change VLOOKUP Formula

In some cases, you can’t change your lookup table, so we can change our lookup formula.

Here we have VLOOKUP(B7

So, whatever is in B7, look up in this table. And it can’t find this number, because this is text.

So we’ll change this lookup to text.

I’ve clicked after the B7
I’ll type an & which on my keyboard is Shift + 7
Then two double quotes, which is just an empty string

So now this used to be a number. When we add an empty string, it’s going to automatically become text

I’ll press Enter, and now what’s entered as a number here, it’s converted that to text, so it matches what’s in here.

You can add an empty string in your VLOOKUP formula to convert numbers to text, so the lookups work correctly.

Get the Excel Workbook

You can see other solutions, and get the Excel sample workbook on the VLOOKUP Examples page on my Contextures site.

The zipped file is in xlsx format, and does not contain any macros.

__________________________