G'day

I have a Workbook with multiple sheets where i'm trying to return the name of a person filling a particular role based on the date and the role.

For example.
Sheet: Structure Chart
U9= 'formula'
U8= Incident Controller (Role)
H5= Date

Sheet: Sheet 1
B2:AF2= Row of dates
A3:A38= Role
B3:AF38= name of people filling roles on a particular day.

What I want to happen is that the formula in U9 returns the name of the Incident Controller for the date in H5. The name can be found in Sheet 1 going by the role (incident controller) and the date.

Hope this makes sense.

Try...

=INDEX(Sheet1!$B$3:$AF$38,MATCH($U$8,Sheet1!$A$3:$ A$38,0),MATCH($H$5,Sheet1!$B$2:$AF$2,0))

Awesome, Thank you, got it. I was on the right track but didn't quite have it. Your solution worked, thank you.

You are very welcome and thanks for the feedback.

Here's a helpful reference...
http://www.contextures.com/xlFunctions03.html