Why is my Vlookup not working?

Vlookup is one of the most powerful tools in Excel. The problem is, when you are new to using it and you can’t figure out why it is not working. There are several reasons why your vlookup may not be working. Below are some troubleshooting tips.

YOUR NUMBERS ARE ACTUALLY TEXT

The number one most common reason why a Vlookup does not work is because the numbers in your cells are actually text.

They look like numbers, you even might have went to format and formatted them as numbers… but trust me they are still text.

Before you say “No my numbers are definitely numbers”… check 1 thing.

Do your cells have a green arrow next to them? OR are the centered to the left instead of the right?

If the answer is yes then your numbers are actually stored as text.

Both what you are referencing and what you are looking up need to be the same type. So if one set is text and the other is numbers, your vlookup will return #N/A.

So how do you fix this?

There are three ways…

Method 1:

You can highlight the cells and a little warning box will appear.

Click this warning box and click on “convert to number”

Method 2:

If you are copy and pasting info into a template then the easiest way is to build in converting numbers to text.

Create a formula that multiplies the cell by 1 and it will no longer be text.

Multiply all of the text cells by 1.

Method 3:

The inverse… to make a cell that is a number and turn it into text.

There are a bunch of ways to do this as well.

I prefer to

1. Concatenate an apostrophe to the front of the cell.

=”‘”&A1

OR

Use this formula.

=left(A1,len(A1))

If you are still having issues post a comment below and I’d be happy to help you figure it out!