Index and match to find right range to search

steve1979

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
2shift 1mmmnnndodododo
3shift 2dododommmnnndo
4shift 3dodododododommmn
5Shift 4nnnndododododom
6
7
8
9
10
11
12date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
13mshift 1shift 1shift 1shift 2shift 2shift 2shift 3shift 3shift 3Shift 4
14N
Sheet5
Cell Formulas
RangeFormula
C1:K1C1=B1+1
B13:K13B13=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)
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

20 11 05.xlsm
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
2shift 1mmmnnndodododo
3shift 2dododommmnnndo
4shift 3dodododododommmn
5Shift 4nnnndododododom
6
7
8
9
10
11
12date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
13mshift 1shift 1shift 1shift 2shift 2shift 2shift 3shift 3shift 3Shift 4
14NShift 4Shift 4Shift 4shift 1shift 1shift 1shift 2shift 2shift 2shift 3
Shifts
Cell Formulas
RangeFormula
C1:K1C1=B1+1
B13:K14B13=INDEX($A$2:$A$5,MATCH($A13,FILTER($B$2:$K$5,$B$1:$K$1=B$12),0))
 

steve1979

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
=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
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
2shift 1mmmnnndodododo
3shift 2dododommmnnndo
4shift 3dodododododommmn
5Shift 4nnnndododododom
6
7
8
9
10
11
12date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
13mshift 1shift 3#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!
14N
Sheet5
Cell Formulas
RangeFormula
C1:K1C1=B1+1
B13:K13B13=INDEX($A$2:$A$5,MATCH($A13,INDEX(B$2:K$5,MATCH(B$12,$B$1:$K$1,0),0),0))
 

steve1979

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

20 11 05.xlsm
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
2shift 1mmmnnndodododo
3shift 2dododommmnnndo
4shift 3dodododododommmn
5Shift 4nnnndododododom
6
7
8
9
10
11
12date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
13mshift 1shift 1shift 1shift 2shift 2shift 2shift 3shift 3shift 3Shift 4
14NShift 4Shift 4Shift 4shift 1shift 1shift 1shift 2shift 2shift 2shift 3
Shifts
Cell Formulas
RangeFormula
C1:K1C1=B1+1
B13:K14B13=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
1604575812284.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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?

1604576431417.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
2shift 1mmmnnndodododo
3shift 2dododommmnnndo
4shift 3dodododododommmn
5Shift 4nnnndododododom
6
7
8
9
10
11
12date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
13mshift 1shift 1shift 1shift 2shift 2shift 2shift 3shift 3shift 3Shift 4
14NShift 4Shift 4Shift 4shift 1shift 1shift 1shift 2shift 2shift 2shift 3
Shifts (2)
Cell Formulas
RangeFormula
C1:K1C1=B1+1
B13:K14B13=INDEX($A$2:$A$5,MATCH($A13,INDEX($B$2:$K$5,0,MATCH(B$12,$B$1:$K$1)),0))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,293
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top