Dynamic index to extract values from an array

Question

I do have something similar with the people on one column and their rota next to it for each day of the week.

Managers	Monday	hr	Tuesday	hr	Wednesday	hr
Paolo	10:30	15:30	5.0			0.0			0.0
16:30	23:00	6.5			0.0			0.0
Ettore			0.0			0.0			0.0
		0.0			0.0			0.0
Luca			0.0			0.0			0.0
15:30	23:00	7.5			0.0			0.0
Amaniel			0.0			0.0			0.0
16:00	01:00	9.0			0.0			0.0

In a different sheet I want to create a list of people that are working in the morning (upper row) and dinner (lower row) and the list should update every time I assign a different shift pattern on the day.

Managers
Name	Shift	Start	Finish	Section		Name	Shift	Start	Finish	Section
Paolo	10:30	15:30					Paolo	16:30	23:00
							Luca	15:30	23:00
							Amaniel	16:00	01:00

The idea is that if I put Ettore working at 11:30 am until 16:00 in the first table, the second table will show me Ettore under Paolo with the correct timing. At the same time, Ettore is not working for dinner, so on the second table there shouldn't be any empty space between Paolo and Luca.

The logical idea is to navigate inside the array, check if Paolo is working in the morning; if he is working than write Paolo and his shift, and carry on for the next employee, if not jump on the second row and check Ettore and so on.

Same would be for the dinner, logical test to see if they work or not (not working is an empty cell with no timing/value)

these are the formula I am using at the moment, I have tried adding the match and vlookup but I couldn't integrate in the current formula. Any help???

Name	Shift	Start	Finish	Section
=IF(C5="","",INDEX(Sheet1!$A$3:$U$10,$A5,1))	=IF(INDEX(Sheet1!$A$3:$U$10,$A5,2)>0,INDEX(Sheet1!$A$3:$U$10,$A5,2),"")	=IF(INDEX(Sheet1!$A$3:$U$10,$A5,3)>0,INDEX(Sheet1!$A$3:$U$10,$A5,3),"")

Answer 1

A13 :

=IF(INDIRECT("B"&(ROW(A1)*2))="","",INDIRECT("A"&(ROW(A1)*2)))

B13:

=IFERROR(VLOOKUP($A13,$A$2:$C$9,COLUMN(),0),"")

I13:

=IF(INDIRECT("B"&(ROW(A1)*2+1))="","",INDIRECT("A"&(ROW(A1)*2)))

J13:

=IFERROR(INDEX(B2:B9,MATCH(I13,A2:A9,0)+1),"")

K13:

=IFERROR(INDEX(C2:C9,MATCH(I13,A2:A9,0)+1),"")

I will send the test file to you via private message. You can check it by clicking the icon below.

Answer 2

Hi Snow, unfortunately the result is not what I was looking for.

Managers
Name	Shift	Start	Finish	Section		Name	Shift	Start	Finish	Section
Paolo	10:30						Paolo	16:30	23:00
Ettore	11:30
							Luca	15:30	23:00
							Amaniel	16:00	01:00

This is what I have in result.

What I need is something like

Paolo 10:30 15:30

Ettore 11:30 15:30

And if I do the same on the right side of the table it will shows me the dinner shift corresponding to the lower row of each name.

Realistically it should read line by line the array and understand that 1 line is for the morning shift (left side of the result table) and 1 line will be for the dinner shift (right side of the table), so it will need probably to jump 1 row each time.

I did add the row number on the left of the results, 1-3-5-7-9 so somehow I can tell him to read line 1, line 3, line 5. For the dinner shift it will be +1 to let it read the other row.

But I was using the index function

Answer 3

Filter function is available for these versions.

Answer 4

=FILTER(A2:C9,(A2:A9<>"")*(B2:B9<>""))

Be sure to leave enough space to put the array formula result.

Hi Snow Lu,

For some reason it doesn't recognise the function FILTER. I have only FILTERXML for some reason.

Also if I do A2:A9<>"")*(B2:B9<>"") it means it will check both of them, but the second test will consider the night shifts as well.

Now the problem is that I need separate results in two separate areas of the table, on the left morning (which refers to the upper row of each employee ) and on the right the dinner (lower row of each employee).

Thanks

Answer 5

=FILTER(A2:C9,(A2:A9<>"")*(B2:B9<>""))

Be sure to leave enough space to put the array formula result.