VLOOKUP reference issue

[Mike]

Okay, so I have a table that I've copied and pasted into a new worksheet,
call it Sheet1. Column C in Sheet1 is a list of UPCs that I've converted to
# formats via the Text to Columns function. When I use the formula
"VLOOKUP(C4,'Spin Report 080309 Pivot'!$B$6:$BK$117,59,FALSE)" it returns a
value of "#N/A". I have found two ways to get this to return the correct
value but neither of which is feasible given the fact that I have a list of
1000s of UPCs.

First, putting the UPC in quotes in place of C4 within the formula
(VLOOKUP("###",'Spin Report 080309 Pivot'!$B$6:$BK$117,59,FALSE) returns the
correct value. And second, adding an apostrophe in the formula bar before
the number in column C (changing ### to '###) returns the correct value with
the original formula.

I need Excel to look at column C as the value that appears in the cell or I
need a quick way to add an apostrophe before each value in column C. Any
thoughts?

Thanks.

 

[Jim Thomlinson]

C4 is a number and the values in the lookup range are text. Based on your
descrition you can try

=VLOOKUP(text(C4, "#"),'Spin Report 080309 Pivot'!$B$6:$BK$117,59,FALSE)

 

[Mike]

I did try that with no luck. Thanks for taking the time though!

 

[Mike]

Worked like a charm! Thanks Jim.

C4 is a number and the values in the lookup range are text. Based on your
descrition you can try

=VLOOKUP(text(C4, "#"),'Spin Report 080309 Pivot'!$B$6:$BK$117,59,FALSE)