Index match

[carlidee]

Hi - I'm looking for help building an index match that will help me look up what ranking someone's assignment is (Tab 1) against their stated preferences (Tab 2). Example below:


Tab 1: List of assignments - DESIRED OUTCOME IN THIRD COLUMN

Name	Assignment	Preference number
Joe Smith	Baseball	1st
Anne Brown	Football	2nd
Lisa Jones	Soccer	1st

Tab 2: Preference submission (look up here)

Name	1st	2nd	3rd
Joe Smith	Baseball	Tennis	Soccer
Anne Brown	Soccer	Football	Tennis
Lisa Jones	Soccer	Football	Baseball

 

[Jeffrey Mahoney]

Please put this formula in Cell C2 if the top table starts in cell A1

=INDEX($A$8:$D$8,1,MATCH($B2,OFFSET($A$8,MATCH(A2,$A$8:$A$11,0)-1,0,1,4),0))