Excel Multiple Search and return Non Array formula

[Biz]

Hi,
I have difficult question which I need non array formula in column B which looks at column A reference uses wildcard match Column an returns vales from Column E.


Column A Column B Column C Column D Column E

Name	Group Name	Should Be		Search For	Return Value
Marvel (V) Ltd		Tom		(V)	Tom
Dr Strange (V) (C) Ltd		Tom		[C]	Harry
Black Panther [C] (C) Ltd		Harry		(C)	Mary
Spiderman (C) Ltd		Mary

<tbody>
</tbody>

Your help would be greatly appreciated.

Kind Regards

Biz

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...rch-return-non-array-formula.html#post5331763

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).