Vlookup #N/A error due to formatting

[Guest]

I'm linking a report worksheet to a data pull page, and in cell c4 in the
report, I have the formula: =TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0")
which gives me 1530002038 as the result. This cell is formatted as General.

I'm using vlookup on the report to find this item code in the product spec
sheet, using the formula: =VLOOKUP(C4,oldupc,5,FALSE), but I get the #N/A
error. I've used vlookup a million times, and I've had problems before with
formatting. I've used the EXACT function and Frank Kabel's
=CODE(MID($A$1,COLUMN(A:A),1)) formula (thanks, Frank) to determine these
cell have identical information.

Any ideas on what else I can check or correct to make the vlookup find the
product code in the spec sheet?

As always, any help is appreciated.

 

[Guest]

Try

=VLOOKUP(--C4,oldupc,5,FALSE)


Regards,

Peo Sjoblom

 

[Guest]

Thanks, Peo, that worked! Can you tell me what the "--" in front of the C4
do to the formula? I've never seen that used before.

Thanks again for your help and quick response.

Patrick

 

[Guest]

It just forces the value in C4 to be numeric, you could use 0+C4 or 1*C4 as
well
It is quite frequently used as coercing TRUE/FALSE into 1/0 in sumproduct
formulas
Since the formula you have in C4 will return a text value, you could
probably simplify that formula to

=--RIGHT('15300 UPC Data Pull'!C4,10)

or if indeed the text function is needed you can do the numeric coercing
directly

=--TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0")

then use

=VLOOKUP(C4,oldupc,5,FALSE)

Regards,

Peo Sjoblom

 

[Guest]

you could also try Data->Text to Columns on the column, then it should work