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)
 

steve1979

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

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Peter_SSs

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

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,130
Members
414,043
Latest member
thomas Stein

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