Excel formula for finding text in a string, to return text from cell in the same row

Question

I have an odd question I could do with some expert advice on. I have two tables in Excel, in different sheets:

• in Table 1 I have a list of cities (C:C), each one with a unique numerical identifier (B:B), to account for cities that have the same name despite being in different countries (e.g. Dublin being in Ireland as well as California and Texas, for example);
• in Table 2 I have a list of "groups", each group being a continent/geographical area (B:B), each one with a list of cities that fit under that area, with the city name and its unique numerical identifier (D:D).

I now need to create Table 3 (in a separate sheet), which has the following layout:

• A:A has the names of the cities
• B:B asks is the city is part of a group (to return a YES or NO)
• C:C returns the name of the group, if the city is part of one, or just a blank cell, if the city is not part of a group.

I can't seem to think of formulas that can fit in Table 3's B and C columns. Would this be a case of INDEX and MATCH? Any help is greatly appreciated. (I've attached a screenshot as an example.)

Screenshot from example

Edit:

• I'm using Excel on Mac (Big Sur)
• My Excel version is the latest stable release, 16.47.

Answer 1

You might use this formula in N2 of your posted worksheet and copy down.

=INDEX(G2:G3,MATCH("*" & L2 &"*",I2:I3,0)).

Perhaps it makes sense to include the trailing opening parenthesis in the match:-

=INDEX(G2:G3,MATCH("*" & L2 &"(*",I2:I3,0)).

In cell M2 you might use a formula like this one:-

=IF(COUNTIF(C:C,L2)>1,"Yes","No")