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.

Employee	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
A	OFF	2	3	4	5	1	OFF
B	OFF	OFF	4	5	1	2	1
C	1	OFF	OFF	1	2	3	2
D	2	3	OFF	OFF	3	4	3
E	3	4	5	OFF	OFF	5	4
F	4	5	1	2	OFF	OFF	5
G	5	1	2	3	4	OFF	OFF

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

 

[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?

Employee	Sunday Pos	Sun Start time	Monday Pos	Mon Start time	Tuesday Pos	Tue Start time	Wednesday Pos	Wed Start time	Thursday Pos	Thu Start Time	Friday Pos	Fri Start time	Saturday Pos	Sat Start time
Alex	OFF		2	5	3	9	1	12	1	12	1	5	OFF
Bob	OFF		OFF		1	12	1	12	1	5	2	5	1	5
Chris	1	5	OFF		OFF		1	5	2	5	3	9	2	5
Dan	2	5	3	9	OFF		OFF		3	9	1	12	3	9
Eric	3	9	1	12	1	12	OFF		OFF		1	12	1	12
Frank	1	12	1	12	1	5	2	5	OFF		OFF		1	12
Greg	2	12	1	5	2	5	3	9	1	12	OFF		OFF

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

 

[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?

Employee	Sunday Pos	Sun Start time	Monday Pos	Mon Start time	Tuesday Pos	Tue Start time	Wednesday Pos	Wed Start time	Thursday Pos	Thu Start Time	Friday Pos	Fri Start time	Saturday Pos	Sat Start time
Alex	OFF		2	5	3	9	1	12	1	12	1	5	OFF
Bob	OFF		OFF		1	12	1	12	1	5	2	5	1	5
Chris	1	5	OFF		OFF		1	5	2	5	3	9	2	5
Dan	2	5	3	9	OFF		OFF		3	9	1	12	3	9
Eric	3	9	1	12	1	12	OFF		OFF		1	12	1	12
Frank	1	12	1	12	1	5	2	5	OFF		OFF		1	12
Greg	2	12	1	5	2	5	3	9	1	12	OFF		OFF

<tbody>
</tbody>

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))