CODEWITHSUNDEEP
excelexcel-formula
SoftTimur
SoftTimur

Reputation: 5510

Dynamic formulas for match with several criteria

I have a small table in C3:D11. The formula in G3 is =UNIQUE(C3:C11), which is dynamic array. I manually list several numbers in H3:K3.

Then I would like to fill in the table H3:K8. Let's take H3 for example. If in the raw table, there exists a row whose first element is A and second element is 1, then H3 is TRUE. The formula =NOT(ISERROR(XMATCH(1,($G3=$C$3:$C$11)*(H$2=$D$3:$D$11),FALSE))) works fine (although i'm not sure if it is the best). Then we could copy paste the formula to the whole table.

enter image description here

However, the problem of the above table is that it is not dynamic; it does not spill. I'm thinking how to write a dynamic formula that could spill. I tried in H3 =NOT(ISERROR(XMATCH(1,($G3#=$C$3:$C$11)*(H$2=$D$3:$D$11),FALSE))). It did work for H3, but it did not spill to the rest of Column H.

Does anyone know how to write dynamic formulas to achieve this match with multiple criteria?

enter image description here

Upvotes: 2

Views: 560

Answers (2)

mark fitzpatrick
mark fitzpatrick

Reputation: 3320

You could do:

=ISNUMBER(MATCH(G3#&H2:K2,C3:C11&D3:D11,0))

The To-One-up-Tom-Solution ;-)

If you wanted the whole table created by the formula dynamically, you could do:

=LET( array, C3:D11,
       ltrs, INDEX(array,,1), nmbrs, INDEX(array,,2),
       uLtrs, UNIQUE(ltrs), lSeq, SEQUENCE(ROWS(uLtrs)+1,,0),
       uNmbrs, UNIQUE(nmbrs), nSeq, SEQUENCE(1,ROWS(uNmbrs)+1,0),
       bools, ISNUMBER(MATCH(uLtrs&TRANSPOSE(uNmbrs),ltrs&nmbrs,0)),
       yNotCreateTheWholeTableTom, IF((lSeq=0)*(nSeq=0),"table",
                IF(lSeq=0,INDEX(TRANSPOSE(uNmbrs),1,nSeq),
                    IF(nSeq=0,INDEX(uLtrs,lSeq,),INDEX(bools,lSeq,nSeq)) ) ),
       yNotCreateTheWholeTableTom )

Where C3:D11 is the input to a LET formula that goes into array. All other variable names are there to make the process clear or for comedic purposes.

Upvotes: 2

Tom Sharpe
Tom Sharpe

Reputation: 34220

I think I would use Countifs, although Match or Xmatch should be possible:

=COUNTIFS(C3:C100,UNIQUE(SORT(FILTER(C3:C100,C3:C100<>""))),D3:D100,TRANSPOSE(SORT(UNIQUE(FILTER(D3:D100,C3:C100<>"")))))>0

in H3.

I think it would be awkward to try and do the whole thing with one big formula, so I would use a separate formula for the columns headers and row headers:

=TRANSPOSE(SORT(UNIQUE(FILTER(D3:D100,C3:C100<>""))))

and

=UNIQUE(SORT(FILTER(C3:C100,C3:C100<>"")))

enter image description here

Of course (having looked at @Mark Fitzpatrick's answer!) I could just have put

=COUNTIFS(C3:C100,G3#,D3:D100,H2#)>0

for the first formula.

Upvotes: 3

Related Questions