I'm trying to look up lookup up some lengthy text values in a table. I'm
getting #VALUE! error messages when my lookup up text value is 255
characters. This appears to be an Excel limitation.

Can anyone confirm this and offer a potential alternative?

Thanks,


Cordell

Can't confirm the limitation, but can you lookup left(a1,10) or something
that returns and looks up only the first X values?
************
Anne Troy
www.OfficeArticles.com

"Cordell" wrote in message
...
I'm trying to look up lookup up some lengthy text values in a table. I'm
getting #VALUE! error messages when my lookup up text value is 255
characters. This appears to be an Excel limitation.

Can anyone confirm this and offer a potential alternative?

Thanks,


Cordell

Yes, I can. However, given the nature of the data I'm looking up, I'd prefer
an exact match over the first x characters (which x appears to max out at
255).
--
Cordell


"Anne Troy" wrote:

Can't confirm the limitation, but can you lookup left(a1,10) or something
that returns and looks up only the first X values?
************
Anne Troy
www.OfficeArticles.com

"Cordell" wrote in message
...
I'm trying to look up lookup up some lengthy text values in a table. I'm
getting #VALUE! error messages when my lookup up text value is 255
characters. This appears to be an Excel limitation.

Can anyone confirm this and offer a potential alternative?

Thanks,


Cordell

I got the same error result using xl2003.

If there's no way to use a short key value, maybe you could split that long
value into multiple cells in the same row (each part not exceeding 255
characters).

But then you'd have to split that lookup value the same way, too.

If that's a possibility, then this kind of formula may work for you:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.



Cordell wrote:

I'm trying to look up lookup up some lengthy text values in a table. I'm
getting #VALUE! error messages when my lookup up text value is 255
characters. This appears to be an Excel limitation.

Can anyone confirm this and offer a potential alternative?

Thanks,

Cordell

--

Dave Peterson

Cordell,

You could try matching against the first 255 characters, like so

=MATCH(LEFT(A1,255),LEFT(N1:N2,255),0)

which is an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Cordell" wrote in message
...
I'm trying to look up lookup up some lengthy text values in a table. I'm
getting #VALUE! error messages when my lookup up text value is 255
characters. This appears to be an Excel limitation.

Can anyone confirm this and offer a potential alternative?

Thanks,


Cordell

Great idea Dave. Thanks. I'll use this if I have to split my text, which
I'm not looking forward to as some will regularly be 1,000 characters long.

It is odd that a cell can have 32,767 characters but the lookup functions
will not allow for over 255.

Thanks again for the slick formula.


Cordell


"Dave Peterson" wrote:

I got the same error result using xl2003.

If there's no way to use a short key value, maybe you could split that long
value into multiple cells in the same row (each part not exceeding 255
characters).

But then you'd have to split that lookup value the same way, too.

If that's a possibility, then this kind of formula may work for you:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.



Cordell wrote:

I'm trying to look up lookup up some lengthy text values in a table. I'm
getting #VALUE! error messages when my lookup up text value is 255
characters. This appears to be an Excel limitation.

Can anyone confirm this and offer a potential alternative?

Thanks,

Cordell

--

Dave Peterson

That 255 shows up in lots of places.

Retrieving long strings from a closed workbook.
Even in Autofilter:
http://contextures.com/xlautofilter02.html#String

Cordell wrote:

Great idea Dave. Thanks. I'll use this if I have to split my text, which
I'm not looking forward to as some will regularly be 1,000 characters long.

It is odd that a cell can have 32,767 characters but the lookup functions
will not allow for over 255.

Thanks again for the slick formula.


Cordell

"Dave Peterson" wrote:

I got the same error result using xl2003.

If there's no way to use a short key value, maybe you could split that long
value into multiple cells in the same row (each part not exceeding 255
characters).

But then you'd have to split that lookup value the same way, too.

If that's a possibility, then this kind of formula may work for you:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.



Cordell wrote:

I'm trying to look up lookup up some lengthy text values in a table. I'm
getting #VALUE! error messages when my lookup up text value is 255
characters. This appears to be an Excel limitation.

Can anyone confirm this and offer a potential alternative?

Thanks,

Cordell

--

Dave Peterson


--

Dave Peterson