MATCH Limitation?

[Guest]

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

 

[Anne Troy]

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

 

[Guest]

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

 

[Dave Peterson]

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.

 

[Bob Phillips]

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)

 

[Guest]

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]

That 255 shows up in lots of places.

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