How To CONCAT With A Wildcard

[excelbytes]

As detailed in the image, I need to do a lookup based on two factors: Name & Code. However, the Code in the lookup array may be in a string of codes. Normally, in a situation like this, I would add helper columns that would concatenate the name and code and do a lookup in the second table. However, I'm not sure how to do that while incorporating a wildcard. The obvious results in column C would be ONE, THREE, TWO, FOUR in rows 2 through 5.

Thanks in advance for your help.

 

[Fluff]

How about

=XLOOKUP(A3&"*,"&B3&"*",$E$2:$E$5&","&$F$2:$F$5,$G$2:$G$5,"?",2)

 

[excelbytes]

Fluff, I modified your formula as follows:
=XLOOKUP(A2&"*"&B2&"*",$E$2:$E$5&", "&$F$2:$F$5,$G$2:$G$5,"?",2)
and it works fine. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.