Two-value combination extraction formula?

[Alexinho]

I have a long formula as below which checks how many combinations of two specific subjects there are.

=IFNA(IF(AND(MATCH($A2,$Z$3:$AB$3,0),MATCH(C$1,$Z$3:$AB$3,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$4:$AB$4,0),MATCH(C$1,$Z$4:$AB$4,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$5:$AB$5,0),MATCH(C$1,$Z$5:$AB$5,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$6:$AB$6,0),MATCH(C$1,$Z$6:$AB$6,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$7:$AB$7,0),MATCH(C$1,$Z$7:$AB$7,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$8:$AB$8,0),MATCH(C$1,$Z$8:$AB$8,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$9:$AB$9,0),MATCH(C$1,$Z$9:$AB$9,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$10:$AB$10,0),MATCH(C$1,$Z$10:$AB$10,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$11:$AB$11,0),MATCH(C$1,$Z$11:$AB$11,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$12:$AB$12,0),MATCH(C$1,$Z$12:$AB$12,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$13:$AB$13,0),MATCH(C$1,$Z$13:$AB$13,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$14:$AB$14,0),MATCH(C$1,$Z$14:$AB$14,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$15:$AB$15,0),MATCH(C$1,$Z$15:$AB$15,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$16:$AB$16,0),MATCH(C$1,$Z$16:$AB$16,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$17:$AB$17,0),MATCH(C$1,$Z$17:$AB$17,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$18:$AB$18,0),MATCH(C$1,$Z$18:$AB$18,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$19:$AB$19,0),MATCH(C$1,$Z$19:$AB$19,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$20:$AB$20,0),MATCH(C$1,$Z$20:$AB$20,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$21:$AB$21,0),MATCH(C$1,$Z$21:$AB$21,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$22:$AB$22,0),MATCH(C$1,$Z$22:$AB$22,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$23:$AB$23,0),MATCH(C$1,$Z$23:$AB$23,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$24:$AB$24,0),MATCH(C$1,$Z$24:$AB$24,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$25:$AB$25,0),MATCH(C$1,$Z$25:$AB$25,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$26:$AB$26,0),MATCH(C$1,$Z$26:$AB$26,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$27:$AB$27,0),MATCH(C$1,$Z$27:$AB$27,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$28:$AB$28,0),MATCH(C$1,$Z$28:$AB$28,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$29:$AB$29,0),MATCH(C$1,$Z$29:$AB$29,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$30:$AB$30,0),MATCH(C$1,$Z$30:$AB$30,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$31:$AB$31,0),MATCH(C$1,$Z$31:$AB$31,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$32:$AB$32,0),MATCH(C$1,$Z$32:$AB$32,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$33:$AB$33,0),MATCH(C$1,$Z$33:$AB$33,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$34:$AB$34,0),MATCH(C$1,$Z$34:$AB$34,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$35:$AB$35,0),MATCH(C$1,$Z$35:$AB$35,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$36:$AB$36,0),MATCH(C$1,$Z$36:$AB$36,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$37:$AB$37,0),MATCH(C$1,$Z$37:$AB$37,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$38:$AB$38,0),MATCH(C$1,$Z$38:$AB$38,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$39:$AB$39,0),MATCH(C$1,$Z$39:$AB$39,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$40:$AB$40,0),MATCH(C$1,$Z$40:$AB$40,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$41:$AB$41,0),MATCH(C$1,$Z$41:$AB$41,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$42:$AB$42,0),MATCH(C$1,$Z$42:$AB$42,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$43:$AB$43,0),MATCH(C$1,$Z$43:$AB$43,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$44:$AB$44,0),MATCH(C$1,$Z$44:$AB$44,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$45:$AB$45,0),MATCH(C$1,$Z$45:$AB$45,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$46:$AB$46,0),MATCH(C$1,$Z$46:$AB$46,0))=TRUE,1,0),0)+IFNA(IF(AND(MATCH($A2,$Z$47:$AB$47,0),MATCH(C$1,$Z$47:$AB$47,0))=TRUE,1,0),0)

Essentially it matches the subject in column A and the subject in row 1 to each row in the array z:ab. It tallies the number of rows which contain both subjects in it. Due to the size of the formula, it can get a bit laggy, so I was wondering if there was any more efficient way of doing this?

 

[kvsrinivasamurthy]

Try this . Simpler than my previous solution.
Data taken
Table range A1:G7 . Option table range Z3:AB9, Change as required.
In B2

=LET(a,A2:A7,b,B1:G1,rng,Z3:AB9,d,$Z$3:$AB$3,z,MAKEARRAY(ROWS(a),COLUMNS(b),LAMBDA(r,c,IF(r>=c,"",SUM(1*(MMULT((rng=INDEX(a,r))+(rng=INDEX(b,c)),1*TRANSPOSE(COLUMN(d)>0))=2))))),z)

 

[bebo021999]

In C2:

=SUMPRODUCT((ISNUMBER(SEARCH("-" & $A2&"-" & C$1 & "-","-"&$Z$3:$Z$47&"-"&$AA$3:$AA$47&"-"&$AB$3:$AB$47&"-"))
+ISNUMBER(SEARCH("-" & $C$1&"-" & $A2 & "-","-"&$Z$3:$Z$47&"-"&$AA$3:$AA$47&"-"&$AB$3:$AB$47&"-"))))