Vlookup restricted to 255 String Search ?

[SamBo1234]

Hi all,

Been working with some rather lengthy data, and have just finally after about 2 hours of nearly pulling my hair out realised that VLOOKUP only works with strings = to or less than 255 Characters.

Is there anyway around this problem?

I have strings, approximatley 600+ in some cases and would rather not split the strings up so i can perform a simple VLOOKUP function.

Any help is appreciated. TY all.

 

[yoked]

Hi hatman,

first thank you for your help. It works, but there is one problem.

Here an example:
I'm searching for string "xxx"

Row | Text | Return
1 | xxx faskjdnkkal | Grey
2 | jflkjlkead | Yellow
3 | jlkads xxx faklsdj | blue

When I use your formel I only get "blue" back. Do you know, how I can say Excel, that he should take the first match.

Thanks a lot!

 

[hatman]

I built the formula assuming that there would only be a single match. If you have more than one, then I expect it to return the last match in the table. If you want the first match, the change would be:

=INDEX(C:C,min(IF(ISNUMBER(SEARCH(B9,B2:B4)),ROW(B2:B4),9E10)))

If you want the Nth match from the table, where N is contained in cell A1, then use the following:

=INDEX(C:C,small(IF(ISNUMBER(SEARCH(B9,B2:B4)),ROW(B2:B4),9e10),a1))

Which will generate an error if you specify N greater than the total number of matches...