Second Most Common text value

[DGBedard]

Hello,
I have a spreadsheet where person's names appear in columns A and B and columns C through AF contain alternating attendance (Y or X) indicators and Participation grades (a,b,c)

Example row 3:
John,Doe,Y, a, Y, a, X, b, Y, a, Y, b, Y, c, Y, etc.

In column AG, I would like a formula that tells me the second most common text value (second because "Y" is the most common). for the row above the second most common value would be "a".

Here is what I tried but I get a #NUM error

=INDEX(C3:AF3,MATCH(LARGE(COUNTIF(INDEX(C3:AF3,0),C3:AF3),LARGE(COUNTIF(INDEX(C3:AF3,0),C3:AF3),1)+1),INDEX(COUNTIF(INDEX(C3:AF3,0),C3:AF3),0,),))

Any help would be apprciated

 

[Eric W]

How about:

=INDEX({"a","b","c"},MATCH(MAX(COUNTIF(C3:AF3,{"a","b","c"})),COUNTIF(C3:AF3,{"a","b","c"}),0))

 

[barry houdini]

Another approach, assuming all cells in the range C3:AF3 will be populated

=INDEX(C3:AF3,MODE(IF(C3:AF3<>"Y",MATCH(C3:AF3,C3:AF3,0))))

confirm with CTRL+SHIFT+ENTER

If any cells in the range may be blank you can use this version

=INDEX(C3:AF3,MODE(IF(C3:AF3<>"Y",IF(C3:AF3<>"",MATCH(C3:AF3,C3:AF3,0)))))