Index function with a conditional range

[Gedeon]

Hello,

Here is the spreadsheet I have: (simplified)
This is saying which shift corresponds to which staff every day.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Shift[/TD]
[TD]Staff 1[/TD]
[TD]Staff 2[/TD]
[TD]Staff 3[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]10:00[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]15:00[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]20:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]10:00[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]15:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]20:00[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And here is the output I would want to get:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Function[/TD]
[TD]Shift[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Staff 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Staff 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Staff 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]Staff 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]Staff 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]Staff 3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am looking here for the shift corresponding to each staff on each day. I would like to have only one formula for the whole table as the database is quite big.

I have tried with an INDEX MATCH function and I was able to get the shift for each staff but I couldn't get the "day" variable with it. I would like to do: INDEX(Shift column ONLY for Monday; MATCH("x";INDEX(The whole table;0;MATCH("Staff 1"; Column headers;0);0);0)
But I do not know how to do the "Shift column ONLY for Monday" range.

Do you know any way to do this?

Thank you very much for your help!

Gedeon

 

[Jerry Sullivan]

Hi Gedeon and Welcome to MrExcel,

Let's assume you define a named ranges to make it easier to reference parts of your table:

Name: tblSchedule RefersTo: A1:E22 (your entire table)
Name: lstDays RefersTo: =INDEX(tblSchedule,0,1) (your first column)
Name: lstShifts RefersTo: =INDEX(tblSchedule,0,2) (your second column)
Name: hdrSchedule RefersTo: =INDEX(tblSchedule,1,0) (your first row)

Finding the first instance for a given day of the week is pretty straight-forward using =MATCH(thisday, lstDays,0)
You can find the last instance of a day using an array formula, or helper cells. An alternative would be to have your list of days in ascending order
so you can use =MATCH(thisday, lstDays,1) to efficiently find the last instance of a given day.

Obviously you don't want to sort the day strings alphabetically (Friday, Monday, Saturday....Tuesday).
but you could:
Number the days ("1. Monday", "2. Tuesday".....)
Use serial dates then use the custom number format "dddd" to display 1/1/2001 as "Monday", 1/2/2001 as "Tuesday".....

If you use this second tactic you'll need to use the corresponding serial dates in your second table.
Using this second tactic and the defined names listed above, here's one formula that would work....

In J2, then copied down:

=INDEX(INDEX(tblSchedule,MATCH($H2,lstDays,0),0):
    INDEX(tblSchedule,MATCH($H2,lstDays,1),0) lstShifts,
        MATCH("x",INDEX(tblSchedule,MATCH($H2,lstDays,0),0):
            INDEX(tblSchedule,MATCH($H2,lstDays,1),0) INDEX(tblSchedule,0,
                MATCH(I2,hdrSchedule,0))))

Excel Workbook
	H	I	J
1	Day	Function	Shift
2	Monday	Staff 1	15:00
3	Monday	Staff 2	10:00
4	Monday	Staff 3	15:00
5	Tuesday	Staff 1	20:00
6	Tuesday	Staff 2	10:00
7	Tuesday	Staff 3	10:00
Sheet

There's probably simpler formulas, but this could give you a starting point.

 

[Gedeon]

Hello Jerry,

Thank you very much for your reply!
It is exactly what I was looking and answers the question perfectly.

Using the index(...):index(...) to define the range I was looking for was the key. To find the last occurence of the day, I alternatively added a countif(thisday) to the second match of the index range, and using dates works perfectly, since a date a unique (vs. a day of the week which comes back every week).

My problem is solved, thank you Jerry!

Gedeon