Partial text match with wildcards using a reference table

S

shaggy101

New Member
Joined
Oct 20, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1ClinicTeamIndex
2HB PRI CARE MAI 1 *HBPC*HB PRI CARE MAIMaine HBPC
3HB PRI CARE MAI 2 *HBPC*MAI *GERIMaine Geri
4MAI *GERI* 02MAI *H*Maine HPACT
5MAI *H* 01MAI *SCI/DMaine SCI
6MAI *H* 02MAI COEMaine PCC
7MAI *SCI/D* 01MAI GMS PACT VIRTUALVirtual PACT
8MAI COE 02 *WH*MAI MT BAKERMaine PCC
9MAI GMS PACT VIRTUAL 1 *WH*MAI OLYMPICSMaine PCC
10MAI MT BAKER 01MAI RAINIERMaine PCC
11MAI OLYMPICS 02 *WH*MAI WCMaine WHC
12MAI OLYMPICS 02 *WH*TUC *GERITUC Geri
13MAI RAINIER 03 *WH*TUC *H*TUC HPACT
14MAI RAINIER 03 *WH*TUC GOLDTUC PCC
15MAI RAINIER 03 *WH*TUC ORANGETUC PCC
16MAI RAINIER 04TUC REDTUC PCC
17MAI WC ALKI 06 *WH*TUC SILVERTUC PCC
18MAI WC ALKI 09 *WH*TUC WCTUC WHC
19MAI WC ALKI 10 *WH*TUC YELLOWTUC PCC
20MAI WC CHINOOK 04 *WH*
21MAI WC CHINOOK 06 *WH*
22TUC *GERI* 07 BLUE
23TUC *GERI* 08 BLUE
24TUC *H* 01 *WH*
25TUC GOLD 03
26TUC GOLD 04
27TUC ORANGE 01 *WH*
28TUC RED 04 *WH*
29TUC RED 05
30TUC SILVER 07 *WH*
31TUC SILVER 08
Sheet1
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
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
ABCDE
1ClinicTeamIndex
2Maine HBPC, Maine HPACTHB PRI CARE MAI 1 *HBPC*HB PRI CARE MAIMaine HBPC
3Maine HBPC, Maine HPACTHB PRI CARE MAI 2 *HBPC*MAI *GERIMaine Geri
4Maine GeriMAI *GERI* 02MAI *H*Maine HPACT
5Maine HPACTMAI *H* 01MAI *SCI/DMaine SCI
6Maine HPACTMAI *H* 02MAI COEMaine PCC
7Maine SCIMAI *SCI/D* 01MAI GMS PACT VIRTUALVirtual PACT
8Maine HPACT, Maine PCCMAI COE 02 *WH*MAI MT BAKERMaine PCC
9Maine HPACT, Virtual PACTMAI GMS PACT VIRTUAL 1 *WH*MAI OLYMPICSMaine PCC
10Maine PCCMAI MT BAKER 01MAI RAINIERMaine PCC
11Maine HPACT, Maine PCCMAI OLYMPICS 02 *WH*MAI WCMaine WHC
12Maine HPACT, Maine PCCMAI OLYMPICS 02 *WH*TUC *GERITUC Geri
13Maine HPACT, Maine PCCMAI RAINIER 03 *WH*TUC *H*TUC HPACT
14Maine HPACT, Maine PCCMAI RAINIER 03 *WH*TUC GOLDTUC PCC
15Maine HPACT, Maine PCCMAI RAINIER 03 *WH*TUC ORANGETUC PCC
16Maine PCCMAI RAINIER 04TUC REDTUC PCC
17Maine HPACT, Maine WHCMAI WC ALKI 06 *WH*TUC SILVERTUC PCC
18Maine HPACT, Maine WHCMAI WC ALKI 09 *WH*TUC WCTUC WHC
19Maine HPACT, Maine WHCMAI WC ALKI 10 *WH*TUC YELLOWTUC PCC
20Maine HPACT, Maine WHCMAI WC CHINOOK 04 *WH*
21Maine HPACT, Maine WHCMAI WC CHINOOK 06 *WH*
22TUC GeriTUC *GERI* 07 BLUE
23TUC GeriTUC *GERI* 08 BLUE
24TUC HPACTTUC *H* 01 *WH*
25TUC PCCTUC GOLD 03
26TUC PCCTUC GOLD 04
27TUC HPACT, TUC PCCTUC ORANGE 01 *WH*
28TUC HPACT, TUC PCCTUC RED 04 *WH*
29TUC PCCTUC RED 05
30TUC HPACT, TUC PCCTUC SILVER 07 *WH*
31TUC PCCTUC SILVER 08
Hoja9


Formula in A2:
Excel Formula: 
=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
--------------
 
Upvote 0
Solution
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!
 
Upvote 0
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!
 
Upvote 0
shaggy101 said:
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!
Click to expand...

May be

Book9
ABCDEFG
1ClinicTeamIndex
2Maine HBPCHB PRI CARE MAI 1 *HBPC*HB PRI CARE MAIMaine HBPC
3Maine HBPCHB PRI CARE MAI 2 *HBPC*MAI *GERIMaine Geri
4Maine GeriMAI *GERI* 02MAI *H*Maine HPACT
5Maine HPACTMAI *H* 01MAI *SCI/DMaine SCI
6Maine HPACTMAI *H* 02MAI COEMaine PCC
7Maine SCIMAI *SCI/D* 01MAI GMS PACT VIRTUALVirtual PACT
8Maine HPACTMAI COE 02 *WH*MAI MT BAKERMaine PCC
9Maine HPACTMAI GMS PACT VIRTUAL 1 *WH*MAI OLYMPICSMaine PCC
10Maine PCCMAI MT BAKER 01MAI RAINIERMaine PCC
11Maine HPACTMAI OLYMPICS 02 *WH*MAI WCMaine WHC
12Maine HPACTMAI OLYMPICS 02 *WH*TUC *GERITUC Geri
13Maine HPACTMAI RAINIER 03 *WH*TUC *H*TUC HPACT
14Maine HPACTMAI RAINIER 03 *WH*TUC GOLDTUC PCC
15Maine HPACTMAI RAINIER 03 *WH*TUC ORANGETUC PCC
16Maine PCCMAI RAINIER 04TUC REDTUC PCC
17Maine HPACTMAI WC ALKI 06 *WH*TUC SILVERTUC PCC
18Maine HPACTMAI WC ALKI 09 *WH*TUC WCTUC WHC
19Maine HPACTMAI WC ALKI 10 *WH*TUC YELLOWTUC PCC
20Maine HPACTMAI WC CHINOOK 04 *WH*
21Maine HPACTMAI WC CHINOOK 06 *WH*
22TUC GeriTUC *GERI* 07 BLUE
23TUC GeriTUC *GERI* 08 BLUE
24TUC HPACTTUC *H* 01 *WH*
25TUC PCCTUC GOLD 03
26TUC PCCTUC GOLD 04
27TUC HPACTTUC ORANGE 01 *WH*
28TUC HPACTTUC RED 04 *WH*
29TUC PCCTUC RED 05
30TUC HPACTTUC SILVER 07 *WH*
31TUC PCCTUC SILVER 08
32
Sheet2
Cell Formulas
RangeFormula
A2:A31A2=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))
 
Last edited:
Upvote 0
Sufiyan97 said:
May be

Book9
ABCDEFG
1ClinicTeamIndex
2Maine HBPCHB PRI CARE MAI 1 *HBPC*HB PRI CARE MAIMaine HBPC
3Maine HBPCHB PRI CARE MAI 2 *HBPC*MAI *GERIMaine Geri
4Maine GeriMAI *GERI* 02MAI *H*Maine HPACT
5Maine HPACTMAI *H* 01MAI *SCI/DMaine SCI
6Maine HPACTMAI *H* 02MAI COEMaine PCC
7Maine SCIMAI *SCI/D* 01MAI GMS PACT VIRTUALVirtual PACT
8Maine HPACTMAI COE 02 *WH*MAI MT BAKERMaine PCC
9Maine HPACTMAI GMS PACT VIRTUAL 1 *WH*MAI OLYMPICSMaine PCC
10Maine PCCMAI MT BAKER 01MAI RAINIERMaine PCC
11Maine HPACTMAI OLYMPICS 02 *WH*MAI WCMaine WHC
12Maine HPACTMAI OLYMPICS 02 *WH*TUC *GERITUC Geri
13Maine HPACTMAI RAINIER 03 *WH*TUC *H*TUC HPACT
14Maine HPACTMAI RAINIER 03 *WH*TUC GOLDTUC PCC
15Maine HPACTMAI RAINIER 03 *WH*TUC ORANGETUC PCC
16Maine PCCMAI RAINIER 04TUC REDTUC PCC
17Maine HPACTMAI WC ALKI 06 *WH*TUC SILVERTUC PCC
18Maine HPACTMAI WC ALKI 09 *WH*TUC WCTUC WHC
19Maine HPACTMAI WC ALKI 10 *WH*TUC YELLOWTUC PCC
20Maine HPACTMAI WC CHINOOK 04 *WH*
21Maine HPACTMAI WC CHINOOK 06 *WH*
22TUC GeriTUC *GERI* 07 BLUE
23TUC GeriTUC *GERI* 08 BLUE
24TUC HPACTTUC *H* 01 *WH*
25TUC PCCTUC GOLD 03
26TUC PCCTUC GOLD 04
27TUC HPACTTUC ORANGE 01 *WH*
28TUC HPACTTUC RED 04 *WH*
29TUC PCCTUC RED 05
30TUC HPACTTUC SILVER 07 *WH*
31TUC PCCTUC SILVER 08
32
Sheet2
Cell Formulas
RangeFormula
A2:A31A2=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))
Click to expand...
Yes, this works! Thanks!
 
Upvote 0
shaggy101 said:
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!
Click to expand...

Here another option:
Dante Amor
ABCDE
1ClinicTeamIndex
2Maine HPACTHB PRI CARE MAI 1 *HBPC*HB PRI CARE MAIMaine HBPC
3Maine HPACTHB PRI CARE MAI 2 *HBPC*MAI *GERIMaine Geri
4Maine GeriMAI *GERI* 02MAI *H*Maine HPACT
5Maine HPACTMAI *H* 01MAI *SCI/DMaine SCI
6Maine HPACTMAI *H* 02MAI COEMaine PCC
7Maine SCIMAI *SCI/D* 01MAI GMS PACT VIRTUALVirtual PACT
8Maine PCCMAI COE 02 *WH*MAI MT BAKERMaine PCC
9Virtual PACTMAI GMS PACT VIRTUAL 1 *WH*MAI OLYMPICSMaine PCC
10Maine PCCMAI MT BAKER 01MAI RAINIERMaine PCC
11Maine PCCMAI OLYMPICS 02 *WH*MAI WCMaine WHC
12Maine PCCMAI OLYMPICS 02 *WH*TUC *GERITUC Geri
13Maine PCCMAI RAINIER 03 *WH*TUC *H*TUC HPACT
14Maine PCCMAI RAINIER 03 *WH*TUC GOLDTUC PCC
15Maine PCCMAI RAINIER 03 *WH*TUC ORANGETUC PCC
16Maine PCCMAI RAINIER 04TUC REDTUC PCC
17Maine WHCMAI WC ALKI 06 *WH*TUC SILVERTUC PCC
18Maine WHCMAI WC ALKI 09 *WH*TUC WCTUC WHC
19Maine WHCMAI WC ALKI 10 *WH*TUC YELLOWTUC PCC
20Maine WHCMAI WC CHINOOK 04 *WH*
21Maine WHCMAI WC CHINOOK 06 *WH*
22TUC GeriTUC *GERI* 07 BLUE
23TUC GeriTUC *GERI* 08 BLUE
24TUC HPACTTUC *H* 01 *WH*
25TUC PCCTUC GOLD 03
26TUC PCCTUC GOLD 04
27TUC PCCTUC ORANGE 01 *WH*
28TUC PCCTUC RED 04 *WH*
29TUC PCCTUC RED 05
30TUC PCCTUC SILVER 07 *WH*
31TUC PCCTUC SILVER 08
Hoja9
Cell Formulas
RangeFormula
A2:A31A2=LOOKUP(2,1/SEARCH($D$2:$D$19,B2),$E$2:$E$19)
 
Upvote 0
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
ABCDEFGHIJK
1Transaction DescriptionFull NameFull NameVendor NameDescription1Description2Description3Description4Description5Description6
2ORIG CO NAME=HUMANA INC.Bay Planning#N/ADevoted HealthDEVOTED HEALTH#N/A#N/A#N/A#N/A#N/A
3ORIG CO NAME=GBU FINANCIAL LI#NUM!#N/ACapital BluecrosCAPITAL BLUECROSBLUE CROSS BLUEHMBCBSDEAPMESSER DES:BCBSRegence BCBSUBCBS ILLINOIS
4ORIG CO NAME=S USA Life InsurS. Usa Life#N/AHighmarkapHIGHMARKAP#N/A#N/A#N/A#N/A#N/A
5ORIG CO NAME=AMERICO MED SUPP#NUM!#N/ABay PlanningBAY PLANNINGHUMANA INC#N/A#N/A#N/A#N/A
6ORIG CO NAME=LUMICO LIFE INS#NUM!#N/AS. Usa LifeS. USA LifeS USA Life#N/A#N/A#N/A#N/A
7ORIG CO NAME=Regence BCBSUCapital Bluecros#N/ACaresourceCARESOURCE#N/A#N/A#N/A#N/A#N/A
8ORIG CO NAME=FORESTERS USA#NUM!#N/AHumana-EftHUMANA-EFT#N/A#N/A#N/A#N/A#N/A
9ELECTRONIC DEPOSIT Regence BCBSUCapital Bluecros#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=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:C9C2=LOOKUP(2,1/SEARCH($F$2:$K$9,A2),$E$2:$E$9)
 
Upvote 0
Sufiyan97 said:
LOOKUP(2,1/SEARCH($F$2:$K$9,A2),$E$2:$E$9)
Click to expand...
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 support.microsoft.com

The formula that you have in B2 is correct, perhaps it would be necessary to add an IFERROR.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,019
Messages
6,122,707
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top