How do I return the column header matching multipl

Question

I am trying to do a lookup of a subset of data using multiple criteria and returning the column header that meets the defined criteria. Each unique identifier has received an award from a judge (tab1). I would like to use a formula (or a macro) to return who the judge was that awarded the highest award in tab2.

In the worksheet on the second tab, I have been trying to use an index and match formula in the "Judge" column to accomplish this. However, my formula returns #N/A. My formula is currently configured: {=INDEX(Data!$B$2:$D$2,MATCH(A3&B3,Data!$A$3:$A$12&Data!$B$3:$D$12,0))}

The result should be the judge (Bates, Parks, or Gemini) that awarded the unique identifier the award level defined (the highest). Thanks in advance.

Answer 1

Use:

=INDEX(DATA!$2:$2,MATCH(B3,INDEX(DATA!A:D,MATCH(A3,DATA!A:A,0),0),0))