VLOOKUP Returns #NA when data is present

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
96
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
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

Board Regular
Joined
Mar 1, 2015
Messages
96
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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

Board Regular
Joined
Mar 1, 2015
Messages
96
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
For that number on both sheets use
=LEN()
and
=ISNUMBER()
do you get the same results?
 

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
96
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
Does the ISNUMBER return true for both?
 

Forum statistics

Threads
1,085,546
Messages
5,384,373
Members
401,890
Latest member
Angela7

Some videos you may like

This Week's Hot Topics

Top