excel - Using Index to return max of lists -

any here received. have list of locations , list of faults thus:

controller   error            1 b            1            2            2 b            3 

this many thousands of faults, need formula return name of occurring error given controller using excel.

with table in a1:b6 (with headers in row 1) , choice of controller, e.g. "b", in c1, array formula**:

=index(b$2:b$6,mode(if(a$2:a$6=c1,match(b$2:b$6,b$2:b$6,{0,0}))))

if, chosen controller, no 1 error more frequent other, then, of errors sharing highest frequency, occurs earliest in list returned.

regards

**array formulas not entered in same way 'standard' formulas. instead of pressing enter, first hold down ctrl , shift, , press enter. if you've done correctly, you'll notice excel puts curly brackets {} around formula (though not attempt manually insert these yourself).