#VALUE error-- some cells?
 

I am doing a VLOOKUP moving out comments from a Pivot Table to separate
column. I have 115 rows, 100 of which work perfectly... but 15 of them give
me the #VALUE error stating "A value used in the formula is of the wrong data
type."

The formula I am using is:
=IF(VLOOKUP(LEFT(C19,2500),$C$5:$J$64,1,FALSE)=0," ",(VLOOKUP(LEFT(C19,2500),$C$5:$J$64,1,FALSE)) )

Why does it work for some but not others? Some of the ones not working have
acronyms, HR, CC'd, etc... could that be the problem-- and if so, how can I
get it to ignore them?

Thanks!

#VALUE error-- some cells?
 

Did you really want to use the first 2500 characters of C19 in your =vlookup()
expressions???

=vlookup() is one of those functions that breaks if the lookup value is longer
than 255.





roborat wrote:

I am doing a VLOOKUP moving out comments from a Pivot Table to separate
column. I have 115 rows, 100 of which work perfectly... but 15 of them give
me the #VALUE error stating "A value used in the formula is of the wrong data
type."

The formula I am using is:
=IF(VLOOKUP(LEFT(C19,2500),$C$5:$J$64,1,FALSE)=0," ",(VLOOKUP(LEFT(C19,2500),$C$5:$J$64,1,FALSE)) )

Why does it work for some but not others? Some of the ones not working have
acronyms, HR, CC'd, etc... could that be the problem-- and if so, how can I
get it to ignore them?

Thanks!

--

Dave Peterson

#VALUE error-- some cells?
 

I don't think this formula is doing what you think it is...
For instance,
VLOOKUP(LEFT(C19,2500),AnyRange,1,FALSE)
is going to return a value from the first column, aka C, thereby, this
results in C19.

Also, the logic check will never be met, as the VLOOKUP of a value that
exists (as detemrined from first step) will never be 0.

So, in the end, your formula is simply:
=C19

--
Best Regards,

Luke M
"roborat" wrote in message
...
I am doing a VLOOKUP moving out comments from a Pivot Table to separate
column. I have 115 rows, 100 of which work perfectly... but 15 of them
give
me the #VALUE error stating "A value used in the formula is of the wrong
data
type."

The formula I am using is:
=IF(VLOOKUP(LEFT(C19,2500),$C$5:$J$64,1,FALSE)=0," ",(VLOOKUP(LEFT(C19,2500),$C$5:$J$64,1,FALSE)) )

Why does it work for some but not others? Some of the ones not working
have
acronyms, HR, CC'd, etc... could that be the problem-- and if so, how can
I
get it to ignore them?

Thanks!

#VALUE error-- some cells?
 

I'm not sure why the word "AnyRange" appears in my post...it should have
been what you originally called out "$C$5:$J$64"...
Apologies.

--
Best Regards,

Luke M
"Luke M" wrote in message
...
I don't think this formula is doing what you think it is...
For instance,
VLOOKUP(LEFT(C19,2500),AnyRange,1,FALSE)
is going to return a value from the first column, aka C, thereby, this
results in C19.

Also, the logic check will never be met, as the VLOOKUP of a value that
exists (as detemrined from first step) will never be 0.

So, in the end, your formula is simply:
=C19

--
Best Regards,

Luke M
"roborat" wrote in message
...
I am doing a VLOOKUP moving out comments from a Pivot Table to separate
column. I have 115 rows, 100 of which work perfectly... but 15 of them
give
me the #VALUE error stating "A value used in the formula is of the wrong
data
type."

The formula I am using is:
=IF(VLOOKUP(LEFT(C19,2500),$C$5:$J$64,1,FALSE)=0," ",(VLOOKUP(LEFT(C19,2500),$C$5:$J$64,1,FALSE)) )

Why does it work for some but not others? Some of the ones not working
have
acronyms, HR, CC'd, etc... could that be the problem-- and if so, how can
I
get it to ignore them?

Thanks!