Index and match

[RedOctoberKnight]

Good Morning,

I trying to use index and match to lookup and employee name based on the day of the week and their position.

for example, I want to return the employee name who is in position "1" on Thursday.

[TABLE="width: 485"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]OFF[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[steve the fish]

This would do that:

=INDEX($A$1:$A$8,MATCH(1,INDEX($A$1:$H$8,,MATCH("Thursday",$A$1:$H$1,0)),0))

 

[Scott T]

Excel 2010
	A	B	C	D	E	F	G	H
1	Employee	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
2	A	OFF	2	3	4	5	1	OFF
3	B	OFF	OFF	4	5	1	2	1
4	C	1	OFF	OFF	1	2	3	2
5	D	2	3	OFF	OFF	3	4	3
6	E	3	4	5	OFF	OFF	5	4
7	F	4	5	1	2	OFF	OFF	5
8	G	5	1	2	3	4	OFF	OFF
9
10			1	position to find
11			Thursday	Day to find
12
13			B	employee
Sheet4

Cell Formulas
Range	Formula
C13	=INDEX(A2:A8,MATCH(C10,INDEX(B2:H8,0,MATCH(C11,B1:H1,0)),0))

 

[RedOctoberKnight]

Awesome! Thanks so much!

 

[RedOctoberKnight]

Adding on to this, would I be able to do it by adding a start time? For example, I want to look up who works position 1 on Thursday at start time of 5?

[TABLE="width: 1326"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Sunday Pos[/TD]
[TD]Sun Start time[/TD]
[TD]Monday Pos[/TD]
[TD]Mon Start time[/TD]
[TD]Tuesday Pos[/TD]
[TD]Tue Start time[/TD]
[TD]Wednesday Pos[/TD]
[TD]Wed Start time[/TD]
[TD]Thursday Pos[/TD]
[TD]Thu Start Time[/TD]
[TD]Friday Pos[/TD]
[TD]Fri Start time[/TD]
[TD]Saturday Pos[/TD]
[TD]Sat Start time[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]OFF[/TD]
[TD] [/TD]
[TD]OFF[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[Finalfight40]

Adding on to this, would I be able to do it by adding a start time? For example, I want to look up who works position 1 on Thursday at start time of 5?

[TABLE="width: 1326"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Sunday Pos[/TD]
[TD]Sun Start time[/TD]
[TD]Monday Pos[/TD]
[TD]Mon Start time[/TD]
[TD]Tuesday Pos[/TD]
[TD]Tue Start time[/TD]
[TD]Wednesday Pos[/TD]
[TD]Wed Start time[/TD]
[TD]Thursday Pos[/TD]
[TD]Thu Start Time[/TD]
[TD]Friday Pos[/TD]
[TD]Fri Start time[/TD]
[TD]Saturday Pos[/TD]
[TD]Sat Start time[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Greg[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I think this would work for you:

Its an array so ctrl + shift + enter

=INDEX(A2:A8, MATCH(1, (1=J2:J8)*(5=K2:K8),0))