Why is the MATCH function not working how anticipa

File Located Here (using Google Drive)

Explanation and background: I have a list of animals and their color in column G (animal) and H (color). I have a list of the unique colors in column A, and a list of unique animals in column D. In column B, next to the list of unique colors, I need to know of all the animals, which animal has that color most often (raw number, not proportion.) I cannot use any additional helper cells.

I have established the max by animal for each color with the formula {=MAX(COUNTIFS(H:H,A2,G:G,$D$2:$D$20))} with the result being 7, but that's as far as I can get. I can set the COUNTIFS statement to the MAX statement like so: COUNTIFS(H:H,A2,G:G,E1:E19)=MAX(COUNTIFS(H:H,A2,G:G,E1:E19)) which can be used as a TRUE/FALSE array in an array formula. Finally I try to use MATCH with the previous formula as my array, and look for a TRUE value to attempt to retrieve the position of the only TRUE value in the array, but it seems to not be able to find it and instead gives me 19, which is the length of the entire array.

When I step through the formula, here's the step right before resulting in 19:

step before final result

Why does this MATCH not work?

回答1:

put this in B2 :

=CHOOSE(IF(MAX(COUNTIFS(G:G,$E$1,H:H,A2),COUNTIFS(G:G,$E$2,H:H,A2),COUNTIFS(G:G,$E$3,H:H,A2),COUNTIFS(G:G,$E$4,H:H,A2),COUNTIFS(G:G,$E$5,H:H,A2),COUNTIFS(G:G,$E$6,H:H,A2),COUNTIFS(G:G,$E$7,H:H,A2),COUNTIFS(G:G,$E$8,H:H,A2),COUNTIFS(G:G,$E$9,H:H,A2))>MAX(COUNTIFS(G:G,$E$10,H:H,A2),COUNTIFS(G:G,$E$11,H:H,A2),COUNTIFS(G:G,$E$12,H:H,A2),COUNTIFS(G:G,$E$13,H:H,A2),COUNTIFS(G:G,$E$14,H:H,A2),COUNTIFS(G:G,$E$15,H:H,A2),COUNTIFS(G:G,$E$16,H:H,A2),COUNTIFS(G:G,$E$17,H:H,A2),COUNTIFS(G:G,$E$18,H:H,A2),COUNTIFS(G:G,$E$19,H:H,A2)),1,2),CHOOSE(IF(MAX(COUNTIFS(G:G,$E$1,H:H,A2),COUNTIFS(G:G,$E$2,H:H,A2),COUNTIFS(G:G,$E$3,H:H,A2),COUNTIFS(G:G,$E$4,H:H,A2))>MAX(COUNTIFS(G:G,$E$5,H:H,A2),COUNTIFS(G:G,$E$6,H:H,A2),COUNTIFS(G:G,$E$7,H:H,A2),COUNTIFS(G:G,$E$8,H:H,A2),COUNTIFS(G:G,$E$9,H:H,A2)),1,2),CHOOSE(IF(MAX(COUNTIFS(G:G,$E$1,H:H,A2),COUNTIFS(G:G,$E$2,H:H,A2))>MAX(COUNTIFS(G:G,$E$3,H:H,A2),COUNTIFS(G:G,$E$4,H:H,A2)),1,2),IF(COUNTIFS(G:G,$E$1,H:H,A2)>COUNTIFS(G:G,$E$2,H:H,A2),"bat","raccoon"),IF(COUNTIFS(G:G,$E$3,H:H,A2)>COUNTIFS(G:G,$E$4,H:H,A2),"bear","goat")),CHOOSE(IF(MAX(COUNTIFS(G:G,$E$5,H:H,A2),COUNTIFS(G:G,$E$6,H:H,A2))>MAX(COUNTIFS(G:G,$E$7,H:H,A2),COUNTIFS(G:G,$E$8,H:H,A2),COUNTIFS(G:G,$E$9,H:H,A2)),1,2),IF(COUNTIFS(G:G,$E$5,H:H,A2)>COUNTIFS(G:G,$E$6,H:H,A2),"moose","turtle"),CHOOSE(IF(COUNTIFS(G:G,$E$7,H:H,A2)>MAX(COUNTIFS(G:G,$E$8,H:H,A2),COUNTIFS(G:G,$E$9,H:H,A2)),1,2),"squirrel",IF(COUNTIFS(G:G,$E$8,H:H,A2)>COUNTIFS(G:G,$E$9,H:H,A2),"snake","bird")))),CHOOSE(IF(MAX(COUNTIFS(G:G,$E$10,H:H,A2),COUNTIFS(G:G,$E$11,H:H,A2),COUNTIFS(G:G,$E$12,H:H,A2),COUNTIFS(G:G,$E$13,H:H,A2),COUNTIFS(G:G,$E$14,H:H,A2))>MAX(COUNTIFS(G:G,$E$15,H:H,A2),COUNTIFS(G:G,$E$16,H:H,A2),COUNTIFS(G:G,$E$17,H:H,A2),COUNTIFS(G:G,$E$18,H:H,A2),COUNTIFS(G:G,$E$19,H:H,A2)),1,2),CHOOSE(IF(MAX(COUNTIFS(G:G,$E$10,H:H,A2),COUNTIFS(G:G,$E$11,H:H,A2))>MAX(COUNTIFS(G:G,$E$12,H:H,A2),COUNTIFS(G:G,$E$13,H:H,A2),COUNTIFS(G:G,$E$14,H:H,A2)),1,2),IF(COUNTIFS(G:G,$E$10,H:H,A2)>COUNTIFS(G:G,$E$11,H:H,A2),"cat","dog"),CHOOSE(IF(COUNTIFS(G:G,$E$12,H:H,A2)>MAX(COUNTIFS(G:G,$E$13,H:H,A2),COUNTIFS(G:G,$E$14,H:H,A2)),1,2),"rabbit",IF(COUNTIFS(G:G,$E$13,H:H,A2)>COUNTIFS(G:G,$E$14,H:H,A2),"sheep","cow"))),CHOOSE(IF(MAX(COUNTIFS(G:G,$E$15,H:H,A2),COUNTIFS(G:G,$E$16,H:H,A2))>MAX(COUNTIFS(G:G,$E$17,H:H,A2),COUNTIFS(G:G,$E$18,H:H,A2),COUNTIFS(G:G,$E$19,H:H,A2)),1,2),IF(COUNTIFS(G:G,$E$15,H:H,A2)>COUNTIFS(G:G,$E$16,H:H,A2),"chicken","llama"),CHOOSE(IF(COUNTIFS(G:G,$E$17,H:H,A2)>MAX(COUNTIFS(G:G,$E$18,H:H,A2),COUNTIFS(G:G,$E$19,H:H,A2)),1,2),"pig",IF(COUNTIFS(G:G,$E$18,H:H,A2)>COUNTIFS(G:G,$E$19,H:H,A2),"horse","deer")))))

then drag until B10.

Key things that I learnt here :

Choose() is a good alternative to heavily nested if(). It had somehow helped me to 'not-getting-lost' in the formula breakdown.
Cascaded binary evaluation is a good way to break a list of repeated evaluation.
"I cannot use any additional helper cells." <-- If the OP don't mind, you may always use a helper Sheet instead. Putting this requirement really pushed the limit of excel formula. My 1st version of the solution needs > 10000 characters and it is out excel's 8192 characters limits.

回答2:

Try this array formula in B2 then copy to B3:B10:

= INDEX( $G$1:$G$510, MATCH( MAX( IF( $H$1:$H$510 = A2, COUNTIFS( $G$1:$G$510, $G$1:$G$510, $H$1:$H$510, $H$1:$H$510), "" ) ), IF( $H$1:$H$510 = A2, COUNTIFS( $G$1:$G$510, $G$1:$G$510, $H$1:$H$510, $H$1:$H$510), "" ), 0 ) )