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

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