VLOOKUP Returns #NA when data is present

i8ur4re

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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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"
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
For that number on both sheets use
=LEN()
and
=ISNUMBER()
do you get the same results?
 
Upvote 0
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.
 
Upvote 0
Does the ISNUMBER return true for both?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top