Issue With Lookup 255 Character Limit

Originally Posted by NBVC

Okay,

Try this formula in C2 of Data sheet:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A$2:A$4,AWorkbookin!$A2:$O2)))),B2,"")

copied down

then copy C2:C4 and paste to F2.

Then formula in S2 of other sheet: =IF(AND(Data!C2="Male",Data!F2="Female"),"",Data!C2&"|"&Data!F2) should work.

Hi again NBVC,

Thankyou very much for that formula - i have tested it out and it works except it seems for one thing:

1/ In the 'Data' sheet column A has the list of words and wherever in the list a word is found (or if multiple words matched the last one is 'used') then the value in column B next to the matched word should be used.

I've uploaded Example2 workbook to illustrate.

In the Example2 workbook on the AWorkbookin sheet:

S3 should = Male| (because ccc is found in F3)
S4 should = x| (because dog is found in F4)

Thank you in advance if you can help with the formula revision.