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.