Index and match to find right range to search

[steve1979]

I Have a spread sheet i am stuck on. I have copied a sample of what i am trying to do.
I have a shift roster and am trying to select the shift on shift based on date and what shift is worked. ( Morning or night.) They way i have the formula works but i would like it to select the column it must look at automatically. for example on 1 November it must look for the shift working morning shift and return that shift name. Here is the example

17 oct to 16 november overtime.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
2	shift 1	m	m	m	n	n	n	do	do	do	do
3	shift 2	do	do	do	m	m	m	n	n	n	do
4	shift 3	do	do	do	do	do	do	m	m	m	n
5	Shift 4	n	n	n	n	do	do	do	do	do	m
6
7
8
9
10
11
12	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
13	m	shift 1	shift 1	shift 1	shift 2	shift 2	shift 2	shift 3	shift 3	shift 3	Shift 4
14	N
Sheet5

Cell Formulas
Range	Formula
C1:K1	C1	=B1+1
B13:K13	B13	=INDEX($A$2:$K$5,MATCH($A13,B$2:B$5,0),1)

I would like for it to select the column based on date and then find the shift working morning or night. i can help it by selecting the column to use based on date but i would rather have it do it's own look up. for example on the following formula the bold section is what i want to automate instead of selecting each range and entering it manually. copying does not work as some of the dates are possible on a different column/row. =INDEX($A$2:$K$5;MATCH($A13; B$2:B$5 ;0);1)

 

[RoryA]

I think you mean:

=INDEX($A$2:$A$5,MATCH($A13,INDEX(B$2:K$5,MATCH(B$12,$B$1:$K$1,0)),0))

 

[steve1979]

Hi. i Tried your code but it does not seem to be recognized as a formula by my excel( office 365 pro plus)
If i change the comma's to semi colons it seems to take as a formula, but then gives a #REF Error. but it looks promising i will experiment a bit further.

 

[RoryA]

Apologies, I missed an argument - should have tested before posting. It would be:

=INDEX($A$2:$A$5;MATCH($A13;INDEX(B$2:K$5;MATCH(B$12;$B$1:$K$1;0);0);0))

 

[Peter_SSs]

Since you have Excel 365 then another option to try could be

20 11 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
2	shift 1	m	m	m	n	n	n	do	do	do	do
3	shift 2	do	do	do	m	m	m	n	n	n	do
4	shift 3	do	do	do	do	do	do	m	m	m	n
5	Shift 4	n	n	n	n	do	do	do	do	do	m
6
7
8
9
10
11
12	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
13	m	shift 1	shift 1	shift 1	shift 2	shift 2	shift 2	shift 3	shift 3	shift 3	Shift 4
14	N	Shift 4	Shift 4	Shift 4	shift 1	shift 1	shift 1	shift 2	shift 2	shift 2	shift 3
Shifts

Cell Formulas
Range	Formula
C1:K1	C1	=B1+1
B13:K14	B13	=INDEX($A$2:$A$5,MATCH($A13,FILTER($B$2:$K$5,$B$1:$K$1=B$12),0))

 

[steve1979]

=INDEX($A$2:$A$5;MATCH($A13;INDEX(B$2:K$5;MATCH(B$12;$B$1:$K$1;0);0);0))

the formula only seems to work for the first row and if i change the date it fails

17 oct to 16 november overtime.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
2	shift 1	m	m	m	n	n	n	do	do	do	do
3	shift 2	do	do	do	m	m	m	n	n	n	do
4	shift 3	do	do	do	do	do	do	m	m	m	n
5	Shift 4	n	n	n	n	do	do	do	do	do	m
6
7
8
9
10
11
12	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
13	m	shift 1	shift 3	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
14	N
Sheet5

Cell Formulas
Range	Formula
C1:K1	C1	=B1+1
B13:K13	B13	=INDEX($A$2:$A$5,MATCH($A13,INDEX(B$2:K$5,MATCH(B$12,$B$1:$K$1,0),0),0))

 

[steve1979]

Since you have Excel 365 then another option to try could be

20 11 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
2	shift 1	m	m	m	n	n	n	do	do	do	do
3	shift 2	do	do	do	m	m	m	n	n	n	do
4	shift 3	do	do	do	do	do	do	m	m	m	n
5	Shift 4	n	n	n	n	do	do	do	do	do	m
6
7
8
9
10
11
12	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
13	m	shift 1	shift 1	shift 1	shift 2	shift 2	shift 2	shift 3	shift 3	shift 3	Shift 4
14	N	Shift 4	Shift 4	Shift 4	shift 1	shift 1	shift 1	shift 2	shift 2	shift 2	shift 3
Shifts

Cell Formulas
Range	Formula
C1:K1	C1	=B1+1
B13:K14	B13	=INDEX($A$2:$A$5,MATCH($A13,FILTER($B$2:$K$5,$B$1:$K$1=B$12),0))

i am not sure why but the filter does not seem to work on the office i am using. this is the version i am using if it makes a difference

 

[Peter_SSs]

i am not sure why but the filter does not seem to work on the office i am using. this is the version i am using if it makes a difference

Perhaps you don't (yet) have the FILTER function?
If you click the fx button and choose 'Lookup & Reference' is FILTER in the list?

 

[steve1979]

No i do not have the FILTER function.

Perhaps you don't (yet) have the FILTER function?
If you click the fx button and choose 'Lookup & Reference' is FILTER in the list?

View attachment 25532

Nope no filter function

 

[Peter_SSs]

No i do not have the FILTER function.

You should be getting it before too long, but I can't be certain.

Try this instead.

20 11 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
2	shift 1	m	m	m	n	n	n	do	do	do	do
3	shift 2	do	do	do	m	m	m	n	n	n	do
4	shift 3	do	do	do	do	do	do	m	m	m	n
5	Shift 4	n	n	n	n	do	do	do	do	do	m
6
7
8
9
10
11
12	date	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov
13	m	shift 1	shift 1	shift 1	shift 2	shift 2	shift 2	shift 3	shift 3	shift 3	Shift 4
14	N	Shift 4	Shift 4	Shift 4	shift 1	shift 1	shift 1	shift 2	shift 2	shift 2	shift 3
Shifts (2)

Cell Formulas
Range	Formula
C1:K1	C1	=B1+1
B13:K14	B13	=INDEX($A$2:$A$5,MATCH($A13,INDEX($B$2:$K$5,0,MATCH(B$12,$B$1:$K$1)),0))