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)

 

[steve1979]

Ok . Yes great stuff. The formula works( although it does seem to be rocket engineer level of formulas to me.)

So an easy way to remember to 0 for columns and rows is 0 for row is no rows just column and visa versa.

You are correct in assuming there will be no duplicate dates. ( i use the most basic of formula's to keep the dates honest. ( only the first date is change able and everything else is +1)
The comma to semi colon change is a minor annoyance, but what causes it, where would i find the setting for formula's to use semi colon or comma?

Thanks so much for the help guys.
Ps i made the last formula the correct one. or should i change it back to the first version that worked?

 

[Peter_SSs]

The formula works( although it does seem to be rocket engineer level of formulas to me.)

Thanks so much for the help guys.

Good news & you are welcome. Glad that we could help.
You are doing a reasonably complex calculation so not too surprising that the formula is not overly simple.

The comma to semi colon change is a minor annoyance, but what causes it, where would i find the setting for formula's to use semi colon or comma?

It is related to your system regional settings and/or Excel settings. See here (or Google something like "excel formula list separator") for more information.

Ps i made the last formula the correct one. or should i change it back to the first version that worked?

It is not critical either way. Mark the one you think best answers your question.