Partial text match with wildcards using a reference table

[shaggy101]

Hi there,
I'm trying to have column A here look at the row in column B and do a partial text match based with column D to return the value column E. The database that column B comes from has asterisks as true characters in some names. If columns B and D were flipped, I know how to do a partial text match with xlookup, but I'm stuck in this case when the partial match comes in the lookup array. Any advice much appreciated!

Copy of Sheet.xlsx
	A	B	C	D	E
1	Clinic	Team		Index
2		HB PRI CARE MAI 1 *HBPC*		HB PRI CARE MAI	Maine HBPC
3		HB PRI CARE MAI 2 *HBPC*		MAI *GERI	Maine Geri
4		MAI *GERI* 02		MAI *H*	Maine HPACT
5		MAI *H* 01		MAI *SCI/D	Maine SCI
6		MAI *H* 02		MAI COE	Maine PCC
7		MAI *SCI/D* 01		MAI GMS PACT VIRTUAL	Virtual PACT
8		MAI COE 02 *WH*		MAI MT BAKER	Maine PCC
9		MAI GMS PACT VIRTUAL 1 *WH*		MAI OLYMPICS	Maine PCC
10		MAI MT BAKER 01		MAI RAINIER	Maine PCC
11		MAI OLYMPICS 02 *WH*		MAI WC	Maine WHC
12		MAI OLYMPICS 02 *WH*		TUC *GERI	TUC Geri
13		MAI RAINIER 03 *WH*		TUC *H*	TUC HPACT
14		MAI RAINIER 03 *WH*		TUC GOLD	TUC PCC
15		MAI RAINIER 03 *WH*		TUC ORANGE	TUC PCC
16		MAI RAINIER 04		TUC RED	TUC PCC
17		MAI WC ALKI 06 *WH*		TUC SILVER	TUC PCC
18		MAI WC ALKI 09 *WH*		TUC WC	TUC WHC
19		MAI WC ALKI 10 *WH*		TUC YELLOW	TUC PCC
20		MAI WC CHINOOK 04 *WH*
21		MAI WC CHINOOK 06 *WH*
22		TUC *GERI* 07 BLUE
23		TUC *GERI* 08 BLUE
24		TUC *H* 01 *WH*
25		TUC GOLD 03
26		TUC GOLD 04
27		TUC ORANGE 01 *WH*
28		TUC RED 04 *WH*
29		TUC RED 05
30		TUC SILVER 07 *WH*
31		TUC SILVER 08
Sheet1

 

[Sufiyan97]

Thanks for the clarification.

Is there any way that we can modify to cover multiple rows and columns in LOOKUP formula?