Excel filter table values by multiple conditions-CodePudding

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.

CodePudding user response：

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