Excel filter table values by multiple conditions

Question

I have 2 tables:

one that contains a set of properties: their unique ID, offer type and reference points (for localization) - named Table_Prop
one that contains requests from clients, that are searching for a property: their unique ID, offer type and the reference points they are interested in. - named Table_Req

PROPERTIES TABLE

Property ID Offer type  Reference points
      P1        sell        RP1, RP2, RP3
      P2        sell        RP1, RP4
      P3        rent        RP5
      P4        rent        RP5, RP6
      P5        sell        RP3, RP2

REQUESTS TABLE

Request ID  Offer type  Reference point
    R1         sell          RP1
    R2         rent          RP6
    R3         sell          RP3

All I want is to filter for each request the ID of the matching properties. So I did with this formula:

=TRANSPOSE(FILTER(Table_Prop[Property ID],
(ISNUMBER(SEARCH(Tabel_Req[@[Offer type]], Table_Prop[Offer type], 1))=TRUE)*
(ISNUMBER(SEARCH(Tabel_Req[@[Reference point]], Table_Prop[Reference points]))=TRUE), "NO MATCHES"))

This works just fine, for each requests, it returns:

R1: P1      P2
R2: P4
R3: P1      P5

but 1 client has one or more reference points he is interested in, so the requests table would look like this:

Request ID  Offer type  Reference points
    R1         sell          RP1, RP2
    R2         rent          RP6, RP3
    R3         sell          RP3, RP4

Basically, now, I need a formula that returns all the matching ID Properties that has one of the request's reference points included in the property's reference points.

It should return this:

R1: P1       P2        P5
R2: P4
R3: P1       P2        P5

I found this formula that splits the cell value by "," separator:

=FILTERXML("<t><s>"&SUBSTITUTE(Tabel_Req[@[Reference points]],",","</s><s>")&"</s></t>","//s")

and I tried this formula:

=FILTER(Table_Prop[Property ID], OR(COUNTIF(Table_Prop[@Reference points], "*"&FILTERXML("<t><s>"&SUBSTITUTE(Tabel_Req[@[Reference points]],",","</s><s>")&"</s></t>","//s")&"*"))=TRUE, "NO MATCHES")

but as I expected, it doesn't work.

How can I reach my goal, are there any workarounds? Any help is appreciated.

Note: I wouldn't like to use VBA, just a formula in a cell that returns a list of matching properties.

Because, it filters based on offer type too, R2 has "rent", therefore just P4 matches. — Stroe Gabi, Oct 27 '21 at 13:35

JvdV · Accepted Answer · 2021-10-27T14:20:33.087

Maybe the following is helpfull:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(G2,",","</s><s>")&"</s></t>","//s"),TRANSPOSE(FILTER(A$2:A$6,MMULT(ISNUMBER(FIND(", "&TRANSPOSE(X)&", ",", "&C$2:C$6&", "))*(B$2:B$6=F2),SEQUENCE(COUNTA(X),,,0)))))

Let me try and explain the (thought)proces:

FILTERXML() will first "split" the reference points (input) into an array. For more information on how that works, I'd like to refer to this older post.
In the 2nd step (proces) we will use a known series of functions to test if strings do hold specific substring; ISNUMBER(SEARCH()). The elements fo the previous returned array get leading/trailing comma's and will be multiplied against the result of comparing the Offer type.
MMULT() will then generate a running total of each of the options under 'Reference Poinst'.
The final result (as visualized in the image below) is input for the 2nd parameter under FILTER() and thus returning only those ID's of interest.

There are times that I am amazed by your processes. I am definitely feeling like the old man stuck with his old tool bag complaining about how things were done when he was younger. — Scott Craner, Oct 27 '21 at 18:10
Sir, your truly amazing!, Phenomenal piece of work, you just toy with excel functions. Impressive!!! — Mayukh Bhattacharya, Feb 03 '22 at 14:45

Excel filter table values by multiple conditions

1 Answers1

Related