[Solved] Lookup for multiple values

[bdf1215]

Hello,

I am trying to find any of a table of values as a partial match to a single cell. For instance, in the following single cell:

AA BB CC

I would want to search several terms:

BB
FF
GG
LL
SS
ZZ


So, I'd want a desired result as follows:

AA BB CC 1

DD EE FF GG 1

HH II JJ 0

KK LL MM 1


I've tried Vlookup scenarios, searches, matches, but I can't seem to find the right mix to lookup a partial match from a cell from an entire table of options. I've attached a sample file too.

Sorry if this has been answered, but I could only find searches for single elements per cell, not multiple elements per cell.

[Villeroy]

lookup_substrings.odb

(12.58 KiB) Downloaded 102 times

[bdf1215]

Thanks Villeroy. So no way to handle this in Excel cleanly?

[mikele]

Hello,
take a look at this.
A special case is D11! The SEARCH()-function finds "Co." as well as "Corp" in "IJKL Corp." because "Co." is interpreted as an regular expression.

[bdf1215]

That's perfect! For my purposes, I'm really only interested in 0's and non 0's, so the special case can stay. I'm going to spend some time deconstructing what you have there to make sure I understand it.

You are awesome!