VLOOKUP or equivalent WITH CRITERIA

[shreeja178]

Hi All, Good day!!

Please help me with a formula to lookup every continuous 5 letter word in a cell (separated by comma) and return "YES" if any of the 5 letter word is present in the comparison table. i have attached a file stating the prob and expected result. Any help is greatly appreciated.

Thanks
Shreeja

[FlameRetired]

Copy and paste this formula into C2 and fill down:

Please Login or Register  to view this content.

Does this do what you want?

[shreeja178]

Hi FlameRetired, Thanks for the reply.

This looks good until we have the comparison cells adjacent to each other say in A2 and B2. but i want to look up in the entire A column with B criteria and return the result. is it possible?

[samba_ravi]

See attached file

[FlameRetired]

If I've interpreted correctly:

Please Login or Register  to view this content.

should use entire column for criteria. I tried deleting items from either column and it worked as I interpreted your request. Try it and let me know.

I used criteria / lookup both ways as the example provided seemed to indicate that

HEADER 2 / HEADER 1 and HEADER 1 / HEADER 2 switched roles.