| N1 | N2 | N3 | |
|---|---|---|---|
| A1 | Matt | Tom | Bob |
| A2 | Tom | Bob | Matt |
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?
>Solution :
use a nested INDEX/MATCH:
=INDEX($B$1:$D$1,MATCH(G2,INDEX(B:D,MATCH(G1,A:A,0),0),0))
