I would like to have a function that returns the Column header (N1, N2, N3, N4) based on two cell values. For example, if I have "A1" and "Tom", I want the function to return N2. I have tried the function: =INDEX($B$1:$D$1, SUMPRODUCT(MAX(($B$2:$D$3=G2)*(COLUMN($B$2:$D$3))))-COLUMN($B$1) 1)

(G2 is the lookup value cell)

This formula works great until you have duplicate values like the table above.

Is there another formula that could work better for this?

CodePudding user response：

use a nested INDEX/MATCH:

=INDEX($B$1:$D$1,MATCH(G2,INDEX(B:D,MATCH(G1,A:A,0),0),0))