VLOOKUP Returns #NA when data is present

[i8ur4re]

I cant wrap my head around this error, I have 2 excel sheets, I am using VLOOKUP to add new pricing from the new sheet to the old sheet, I have triple checked a couple of numbers and noticed some exists on both sheets. Yet I it inputs #NA in that cell, I have cross checked a few numbers and saw that the data is visibly on both sheets. What am i doing wrong?

Formula: =VLOOKUP(D2,[Book1]Sheet1!$A$2:$E$139,5,FALSE)

 

[Fluff]

Make sure that they are numbers on both sheets, rather than numbers on one & text on the other.
Also check they are an exact match, depending on how the numbers were created you could have a minor difference due to "floating point errors"

 

[i8ur4re]

I cleaned up the sheet and used =LEN() to see any trailing spaces or invisible characters, I have removed any product numbers that have letters, yet still getting the #NA error. Any chance you'd be willing to take a look at this?

 

[Joe4]

You say two different sheets...
Are those sheets in different files?
If not, what is with the [Book1] reference? It should be unnecessary if both sheets are in the same file.

 

[i8ur4re]

You say two different sheets...
Are those sheets in different files?
If not, what is with the [Book1] reference? It should be unnecessary if both sheets are in the same file.

I just put them in one file, two seperate worksheets. They were in two different files.

I just did a search for a number 714116, this number is in both sheets, yet when i do a search, it tells me nothing was found. I am not sure what im doing wrong at this point.

 

[Joe4]

If they are both in the same file now, remove the [Book1] reference, and make sure that you have the right sheet reference.
Or try using a named range instead, and referencing that.

 

[Fluff]

For that number on both sheets use
=LEN()
and
=ISNUMBER()
do you get the same results?

 

[i8ur4re]

For that number on both sheets use
=LEN()
and
=ISNUMBER()
do you get the same results?

I do see the same results yes, a total of 6 characters. Still getting an #NA

 

[i8ur4re]

If they are both in the same file now, remove the [Book1] reference, and make sure that you have the right sheet reference.
Or try using a named range instead, and referencing that.

I tried that, nothing changed.

 

[Fluff]

Does the ISNUMBER return true for both?