Match to a list with wildcards

[The Gent]

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]

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?

 

[Phuoc]

Try again:

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

 

[The Gent]

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...

=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]

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.

=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]

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

=“*” & $H$3 &  “*”

 

[The Gent]

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.

=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]

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]

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]

This seems to work well...

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

However, this doesn't...

=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]

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.