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.

Try doing the Text to Columns thing on:

'Spin Report 080309 Pivot'!$B$6:$B$117 also... th


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123154

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)
--
HTH...

Jim Thomlinson


"Mike" wrote:

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.

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

"NBVC" wrote:


Try doing the Text to Columns thing on:

'Spin Report 080309 Pivot'!$B$6:$B$117 also... th


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123154

Worked like a charm! Thanks Jim.

"Jim Thomlinson" wrote:

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)
--
HTH...

Jim Thomlinson


"Mike" wrote:

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.