I need some help with a formula including OFFSET, INDEX, MATCH

[Raquel]

Hi! I need some help with a task planner I have prepared. I have a data entry tab and would like that the tasks entered there are shown in a calendar. As there could be several tasks planned for the same day, and several people do to the same task the same day, I'm interested to see all the information in the same cell when needed.

I have built two formulas:
=IFERROR(INDEX(Summary;MATCH($A5;IF(C$4>=TaskStart;IF(C$4<=TaskEnd;Task));0));"")

This is looking the task in column A in the Calendar tab in the tasks table in Data entry sheet; it is fine, but if several people have planned the same task, the same day the formula returns only the first match

=IFERROR(AND(INDEX(Summary;MATCH($A8;IF(C$4>=TaskStart;IF(C$4<=TaskEnd;Task));0));"");IF(AND(WEEKDAY(C$4)>=1;WEEKDAY(C$4)<=7);
SUBSTITUTE(SUBSTITUTE(OFFSET('Data entry'!$J$3;LARGE(IF(C4>=TaskStart;IF(C$4<=TaskEnd;TaskNum;0);0);6);0)
&"
"&OFFSET('Data entry'!$J$3;LARGE(IF(C$4>=TaskStart;IF(C$4<=TaskEnd;TaskNum;0);0);5);0)
&"
"&OFFSET('Data entry'!$J$3;LARGE(IF(C$4>=TaskStart;IF(C$4<=TaskEnd;TaskNum;0);0);4);0)
&"
"&OFFSET('Data entry'!$J$3;LARGE(IF(C$4>=TaskStart;IF(C$4<=TaskEnd;TaskNum;0);0);3);0)
&"
"&OFFSET('Data entry'!$J$3;LARGE(IF(C$4>=TaskStart;IF(C$4<=TaskEnd;TaskNum;0);0);2);0)
&"
"&OFFSET('Data entry'!$J$3;LARGE(IF(C$4>=TaskStart;IF(C$4<=TaskEnd;TaskNum;0);0);1);0)&"
";"0,";"");'Data entry'!$J$3&"
";"");"")&IF(COUNTIF(Holidays;C$4)>0;VLOOKUP(C$4;HolidaysColor;2;FALSE);""))

This is looking the task in column A in the Calendar tab in the tasks table in Data entry sheet; if there are several people with any task planned the same day the formula is returning all the tasks in the same cell, but I want that it returns only the people with one particular task planned for that particular day

Apologies for my very bad explanation/description of the matter!! I have attached the file, I hope it would be clear once you take a look on it.

Thank you so much and Regards!!

Raquel.

 

[PCosta87]

Hi and welcome to the forum

Please refer to the attachment, blue section!

Instead of "MATCH" I went with "SMALL" which allows us to select which match we want (1st, 2nd, 3rd, and so on).
That being said, it was only a matter of replicating the first part of the formula and replacing the "k" of the SMALL function.
I did it for 2 results only (k=1 in the first part and k=2 in the second part), but you should be able to keep adding more.
Remember to complete with Ctrl+Shift+Enter as it is now in array form (won't work properly otherwise).

The problem is that you have to build the formula for a specific number of results which may or may not be problematic if the scheduled tasks for a specific day exceed the number of results the formula accounts for.

In any case, if you have any further questions feel free to ask...

 

[Nebu]

Hi:

If you are interested in non-array solution

=IFERROR(INDEX(Summary,AGGREGATE(15,6,(ROW(Summary)-MIN(ROW(Summary))+1)/($A5=Task)*(B$4>=TaskStart)*(B$4<=TaskEnd),ROW(A1))),"")

Thanks