VLOOKUP reference issue

M

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.
 
J

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)
 
M

Mike

Worked like a charm! Thanks Jim.

Jim Thomlinson said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top