# Match to a list with wildcards

#### The Gent

##### New Member
Good day all,

I am trying to do something which it my head feels quite simple but I can't get a result.

I have four dimensions in a spreadsheet report, lets say: make; model; fuel; engine.

Separately i have a list of exclusions which is a series of the four dimensions that I want to strip out of the report when analysing the report.

My exclusions list is also four dimensions but I want to be able to use wildcards to exclude certain combinations of the report.

The plan is to have a match function in the original report and use a flag to highlight those rows which match the criteria on the exclusions list. In the example below, on the exclusions list I want to exclude any row which has six as the engine, therefore I have used wildcards on the exclusions list for all other dimensions. This doesn't seem to work though.

Any ideas?

For example:

 Report Exclusion Make Model Fuel Engine Exclude? Make Model Fuel Engine Ford Fiesta Petrol Four * * * Six Ford Mondeo Diesel Four Toyota Yaris Petrol Two Jaguar F Petrol Six X Volkswagon Polo Diesel Four

#### The Gent

##### New Member
Or try:

=IF(SUMPRODUCT((B3:E3=\$J\$3:\$M\$3)+(\$J\$3:\$M\$3=""))=4,"X","")
When I expand the range to pick up all exclusions, it doesn't seem to work.

Is there a fix for this?

### Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Phuoc

##### Active Member
Try again:

=IF(SUMPRODUCT(--(MMULT((B3:E3=\$J\$3:\$M\$5)+(\$J\$3:\$M\$5=""),{1;1;1;1})=4)),"X","")

#### The Gent

##### New Member
Try again:

=IF(SUMPRODUCT(--(MMULT((B3:E3=\$J\$3:\$M\$5)+(\$J\$3:\$M\$5=""),{1;1;1;1})=4)),"X","")
This is working on my simple example but when I apply it to my larger dataset it fails...

Excel Formula:
``=IF(SUMPRODUCT(--(MMULT((D2252,F2252,AO2252,AT2252=EXCLUSIONS!A:A,EXCLUSIONS!B:B,EXCLUSIONS!I:I,EXCLUSIONS!L:L)+(EXCLUSIONS!A:A,EXCLUSIONS!B:B,EXCLUSIONS!I:I,EXCLUSIONS!L:L=""),{1;1;1;1})=4)),"X","")``

Any ideas?

#### Xl365

##### New Member
Picking up on an earlier suggestion, you could use four MATCH functions to check for match including wildcards.

If I understand the requirement correctly, this would allow you to exclude for example Jaguar or Jag.
For Jag just type Jag*, for example.

Adjust the range for your set up.

Excel Formula:
``=IF(ISNUMBER(MATCH(\$F\$3,A3,0))+ISNUMBER(MATCH(\$G\$3,B3,0))+ISNUMBER(MATCH(\$H\$3,C3,0))+ISNUMBER(MATCH(\$I\$3,D3,0))>0,"X","")``

#### Xl365

##### New Member

To add that if you want to have the wildcards automatically included, I would suggest you use a helper row to surround the match text with asterisks.
for example
Excel Formula:
``=“*” & \$H\$3 &  “*”``

#### The Gent

##### New Member
Picking up on an earlier suggestion, you could use four MATCH functions to check for match including wildcards.

If I understand the requirement correctly, this would allow you to exclude for example Jaguar or Jag.
For Jag just type Jag*, for example.

Adjust the range for your set up.

Excel Formula:
``=IF(ISNUMBER(MATCH(\$F\$3,A3,0))+ISNUMBER(MATCH(\$G\$3,B3,0))+ISNUMBER(MATCH(\$H\$3,C3,0))+ISNUMBER(MATCH(\$I\$3,D3,0))>0,"X","")``
Hi,

Unfortunately this excludes items even with a partial match...

I will try to use an example from my real data set now.

When I use the above formula it returns the X flag for all items as at least one field is always in the exclusions list.

Below I have shown what the desired X flags would be.

 Sheet 1 - Report​ Column D​ Column F​ Column AO​ Column AT​ Desired Result​ d05​ PL00693​ CA000993​ MA​ d05​ PL00693​ CA001715​ EW​ X​ d05​ PL00693​ CA001715​ MA​ X​ d05​ PL00693​ CA001190​ EW​

 Sheet 2 - Exclusions List​ Column A​ Column B​ Column I​ Column L​ d05​ PL00693​ CA001190​ MA​ d05​ PL00693​ CA001511​ *​ d05​ PL00693​ CA001715​ *​ d05​ PL00693​ CA000984​ *​

#### GraH

##### Well-known Member

Hi,

Thanks for the suggestion. I think this is going to burnout my file though, I should have mentioned I have ~40k rows of data in each report and so it doesn't seem to be able to cope with this.
I tried the formula on 100K rows and it seems to work. How many exclusions are we talking about? I went up to nearly 20K and all my rows were calculated in under 3 seconds.

EDIT: Wait, it did not, my Excel was in freeze modus, I did not even notice.

Maybe an alternative with Power Query or VBA would be more appropriate?

#### The Gent

##### New Member
I tried the formula on 100K rows and it seems to work. How many exclusions are we talking about? I went up to nearly 20K and all my rows were calculated in under 3 seconds.

EDIT: Wait, it did not, my Excel was in freeze modus, I did not even notice.

Maybe an alternative with Power Query or VBA would be more appropriate?
My report is 45k rows and the exclusions are limited to <100.

#### The Gent

##### New Member
This seems to work well...

Excel Formula:
``=IF(SUMPRODUCT(--(MMULT((D6:G6=\$M\$5:\$P\$8)+(\$M\$5:\$P\$8=""),{1;1;1;1})=4)),"X","")``

However, this doesn't...

Excel Formula:
``=IF(SUMPRODUCT(--(MMULT((D6,E6,F6,G6=\$M\$5:\$P\$8)+(\$M\$5:\$P\$8=""),{1;1;1;1})=4)),"X","")``

How can I get this to work when the columns in the report are not alongside each other?

#### Xl365

##### New Member
I am not clear on your exclusion criteria. I understood that you wished to include partial matches.
Why is the MA line not matched?
Are you looking for a match on all columns with the option to have any characters in one or more columns?
If so, change the > 0 to > 3 in the formula I posted.
And link to a 2nd row with the * asterisk wildcard formulas as I mentioned above.
then insert the search terms in the original row. The row below will have eg *Jag* which will be what the match formula looks for.

Typing this on iPhone without Excel.

Just input another character such as # to instead of leaving criteria cells blank, assuming you don’t use the # character in any of the data fields.

Edit: how many exclusions are you looking at?
An alternative method might be to use advanced filter to identify the exclusion rows.

Last edited:

Replies
8
Views
629
Replies
3
Views
262
Replies
2
Views
349
Replies
7
Views
3K
Replies
6
Views
155
Legacy 396030
L