Search code examples
excelsearchmatchcriteria

excel: if cell contains multiple criteria then return multiple criteria, nestled?

I have 4 criteria lists that look like this:

A         B
Name      Category
Name      Category
Name      Category

And a MasterList like this:

A             B
stuffNAME     Category
NAMEstuff     Category
NAME          Category

I would like to know if there is a formula that can search through each criteria list based on the MasterlistA column for "NAME" and return the appropriate "Category" in MasterlistB.

As an example: I'm looking to return MasterlistB based on MasterlistA

Criteria List:

A         B
NBC       NBCU
Disney    ABC

Masterlist:

A                  B   
NYC NBC 20998      NBCU
NJ2987 NBC         NBCU
Disney Florida99   ABC

I'm simplifying, but in reality the criteria lists all refer to different masterlist columns as well. Trying to get the matching/searching part down first.

Solution

  • Given a setup as shown in @ScottCraner 's answer:

    Scott Craner data setup

    In cell E1 and copied down:

    =IFERROR(LOOKUP(1,1/COUNTIF(D1,"*"&$A$1:$A$2&"*"),$B$1:$B$2),"No Match")

    This is a regular formula and does not require array entry.