Trying to create a reverse HLOOKUP scenario

I am attempting to create a multiple worksheet scenario within a ingle file. This is a staff scheduling exercise and uses alphabetic characters to color the cells and these same alphabetic characters is what I am hoping to use in the formula.

In the Monday worksheet employee Tina Smith is scheduled to work in the Restaurant from 8.00 a.m. to 12.00 Midday, therefore the cells contain an "R" but ONLY in the 'Start' of the half hour increments.

I wish to create two formula, one that will identify the starting time cell and another that will identify the ending time cell (plus half hour). These two formula will exist in another worksheet eventually that will contain all staff's starting and ending times by Department for the entire week.

I am attaching a sample of the worksheet.

Sackie01

Try this arrayformula

=IFERROR(MOD(INDEX($A$4:$M$4,MAX(IF(INDEX($B$6:$M$9,MATCH(A16,$A$6:$A$9,0),0)="R",COLUMN($B$4:$M$9)))+1),1),"")

replace "R" with "D" for Deli hours

Not sure if I am doing something incorrect in copying and pasting the above formula into the Excel file, but it returns a time of 6:30 when the above formula is placed in cell C16. I was expecting it to return the value 7:30. Looks like the formula would work for start times, just remove the +1, but not sure, however the issue is the END time. That is formula I need.

You have to array-enter it, Ctrl-Shift-Enter.

Hi Bob,

Please forgive me but I have only heard about arrays once a long time ago. Is there a paper reference that I an acquire that will show me how to create arrays, how to copy arrays etc. Also what makes an array?....what IS a array?...to me an array is a range of cells....I this a correct thought process?...

I did get it to sort of work, ONCE. Now if I change the schedule, the end time does NOT change.

Also would changing the formula to be =IFERROR(MOD(INDEX($A$4:$M$4,MIN(IF(INDEX($B$6:$M$9,MATCH(A12,$A$6:$A$9,0),0)="R",COLUMN($B$4:$M$9)))),1),""), removing the +1 and changing the MAX to MIN, would this work for a start time? ...what identifies the formula as an array?