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)

 

[i8ur4re]

Does the ISNUMBER return true for both?

ISNUMBER returns true on the old price sheet, and false on the new price sheet.

 

[Fluff]

In that case the one on the new sheet is text not a number

 

[i8ur4re]

In that case the one on the new sheet is text not a number

I changed it to numbers, that didnt seem to work, I did copy/pate into a notepad and pasted them back into that column and that seemed to change them to numbers, but i am still seeing NA on all the values.

 

[i8ur4re]

I changed it to numbers, that didnt seem to work, I did copy/pate into a notepad and pasted them back into that column and that seemed to change them to numbers, but i am still seeing NA on all the values.

Here is the link to the document if you dont mind taking a look at this, I have no words for how fraustrating this is.

https://drive.google.com/file/d/1J3Wp1M0FYaf_tcGZ1v6ip2I4SbThGH6r/view?usp=sharing

 

[joeu2004]

https://drive.google.com/file/d/1J3Wp1M0FYaf_tcGZ1v6ip2I4SbThGH6r/view?usp=sharing

I do not see 220175 or any of the 3xxxxx product numbers (D2:D15) in A2:A139 of the "New Prices" worksheet.

I suspect that is true in many/most/all cases.

I do not see 714116 in both worksheets. I see 714116 in "Old Prices", but 714166 in "New Prices".

Use Data > Filter to spot-check product numbers.

-----

Change the range in the VLOOKUP to use absolute cell references, to wit:

=VLOOKUP(D2,'New prices'!$A$2:$I$139,9,FALSE)

Otherwise, your range is changing to A3:A140, A4:A141, etc as you copy the formula down the column.

That might explain why some product numbers cannot be found in both worksheets.

-----

FYI, you can use $I$139 for both VLOOKUPs, instead of $E$139, even though you return the value from relative column 5. IMHO, it just makes things easier.

To that end, you can also change D2 to $D2. That allows you to copy the formula from column K to column L. It is less error-prone.