Help with formula, index match?

[bubblepop]

Hello,

I've been asked to help design a rota system but struggling with some of the more complicated formula's.
I've provided a mock example but the formula required would need to search a row for the date specified, then search that dates column for the dept number and return the staff member covering it. I just can't get my head round what formula is required.

Let me know if you need any more information.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1/04/2018[/TD]
[TD]02/04/2018[/TD]
[TD]03/04/2018[/TD]
[/TR]
[TR]
[TD]Staff 1[/TD]
[TD]Dept 1[/TD]
[TD]Dept 3[/TD]
[TD]Dept 2[/TD]
[/TR]
[TR]
[TD]Staff 2[/TD]
[TD]Dept 3[/TD]
[TD]Dept 1[/TD]
[TD]Dept 1[/TD]
[/TR]
[TR]
[TD]Staff 3[/TD]
[TD]Dept 2[/TD]
[TD]Dept 2[/TD]
[TD]Dept 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date:[/TD]
[TD]User Input data[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dept:[/TD]
[TD]User Input data[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staff Member:[/TD]
[TD]Formula to return result[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[njimack]

Assuming your rota table starts from cell A1, try the following, confirming with ctrl+shift+enter:

=INDEX(A2:A4,MIN(IF(B1:D1=B8,IF(B2:D4=B9,ROW(A2:A4)-1))))

 

[markmzz]

Hi!

Another way in B8 and copy to the right:

=INDEX($A$2:$A$4,MATCH(B$7,INDEX($B$2:$D$4,,MATCH(B$6,$B$1:$D$1,)),))

Markmzz