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

 

[DanteAmor]

Hi, thanks for posting on the forum.

Check if this way works for you, there may be more than one data that matches:

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

Formula in A2:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH($D$2:$D$19,B2)),$E$2:$E$19,""))

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[shaggy101]

Thanks so much for the reply - this works for what it is trying to do, but I should clarify that column A should only have one match, i..e the cells in column B that end in *WH* and *HBPC* should NOT match with the MAI *H*... I feel stuck again trying to modify your formula to meet this criteria... any additional thoughts are appreciated!

 

[shaggy101]

Ah! I changed the D4 cell to MAI ~*H~* and the formula now works for what I need it for! Though it is written to do more than it needs to do and I'm not sure how to simplify it without breaking it. Thank you!

 

[Sufiyan97]

Though it is written to do more than it needs to do and I'm not sure how to simplify it without breaking it. Thank you!

May be

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

Cell Formulas
Range	Formula
A2:A31	A2	=INDEX($E$2:$E$19,AGGREGATE(15,6,ROW($E$2:$E$19)-ROW($E$2)+1/ISNUMBER(SEARCH($D$2:$D$19,B2)),1))

 

[shagan101]

May be

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

Cell Formulas
Range	Formula
A2:A31	A2	=INDEX($E$2:$E$19,AGGREGATE(15,6,ROW($E$2:$E$19)-ROW($E$2)+1/ISNUMBER(SEARCH($D$2:$D$19,B2)),1))

Yes, this works! Thanks!

 

[Sufiyan97]

You're welcome!
Glad we could help.

 

[DanteAmor]

Thanks so much for the reply - this works for what it is trying to do, but I should clarify that column A should only have one match, i..e the cells in column B that end in *WH* and *HBPC* should NOT match with the MAI *H*... I feel stuck again trying to modify your formula to meet this criteria... any additional thoughts are appreciated!

Here another option:

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

Cell Formulas
Range	Formula
A2:A31	A2	=LOOKUP(2,1/SEARCH($D$2:$D$19,B2),$E$2:$E$19)

 

[Sufiyan97]

Hi Dante

Can you please help to update the column C formula if possible, what would be the reason column B formula works and column C formula doesn't

Book11
	A	B	C	D	E	F	G	H	I	J	K
1	Transaction Description	Full Name	Full Name		Vendor Name	Description1	Description2	Description3	Description4	Description5	Description6
2	ORIG CO NAME=HUMANA INC.	Bay Planning	#N/A		Devoted Health	DEVOTED HEALTH	#N/A	#N/A	#N/A	#N/A	#N/A
3	ORIG CO NAME=GBU FINANCIAL LI	#NUM!	#N/A		Capital Bluecros	CAPITAL BLUECROS	BLUE CROSS BLUE	HMBCBSDEAP	MESSER DES:BCBS	Regence BCBSU	BCBS ILLINOIS
4	ORIG CO NAME=S USA Life Insur	S. Usa Life	#N/A		Highmarkap	HIGHMARKAP	#N/A	#N/A	#N/A	#N/A	#N/A
5	ORIG CO NAME=AMERICO MED SUPP	#NUM!	#N/A		Bay Planning	BAY PLANNING	HUMANA INC	#N/A	#N/A	#N/A	#N/A
6	ORIG CO NAME=LUMICO LIFE INS	#NUM!	#N/A		S. Usa Life	S. USA Life	S USA Life	#N/A	#N/A	#N/A	#N/A
7	ORIG CO NAME=Regence BCBSU	Capital Bluecros	#N/A		Caresource	CARESOURCE	#N/A	#N/A	#N/A	#N/A	#N/A
8	ORIG CO NAME=FORESTERS USA	#NUM!	#N/A		Humana-Eft	HUMANA-EFT	#N/A	#N/A	#N/A	#N/A	#N/A
9	ELECTRONIC DEPOSIT Regence BCBSU	Capital Bluecros	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=INDEX($E$2:$E$9,AGGREGATE(15,6,ROW($E$2:$E$9)-ROW($E$2)+1/ISNUMBER(SEARCH($F$2:$K$9,A2)),1))
C2:C9	C2	=LOOKUP(2,1/SEARCH($F$2:$K$9,A2),$E$2:$E$9)

 

[DanteAmor]

LOOKUP(2,1/SEARCH($F$2:$K$9,A2),$E$2:$E$9)

This form of the formula is for vectors, that is, it does not work with a matrix.
A vector is a column or a row.

LOOKUP function - Microsoft Support

How to use the LOOKUP function in Excel to search in a single row or column and find a value from the same position in a second row or column.

support.microsoft.com

The formula that you have in B2 is correct, perhaps it would be necessary to add an IFERROR.