Listing hours according to day of the week

[deciog]

I need to list all hours according to selection expreriencia day of the week

Thank you very much in advance

Decio

 

[Hui]

D18: =IF(COUNTIF(OFFSET(A:A,,MATCH($D$17,$B$2:$G$2,0)),$D$16) < ROWS($D$18:D18), "", INDEX($A:$A, SMALL( IF(OFFSET($A$2:$A$14,,MATCH($D$17,$B$2:$G$2,0)) =$D$16, ROW( OFFSET($A2:$A14,,MATCH($D$17,$B$2:$G$2,0)))), ROW(A1)))) Press Ctrl+Shift+Enter
Then copy down

If you want to understand how that works it is a modified version of: https://chandoo.org/wp/formula-forensics-003/

 

[Hui]

ps: You have mispelt Wednesday in both D2 and G19

 

[deciog]

Hui, Many thanks for the error on Wednesday is the hurry

I put the formula and I painted the result shown red, I could not see where the error is, I ask you to check it, it's almost there

Decio

 

[bosco_yip]

Or.................

In D18, copied down :

=IFERROR(INDEX(A$3:A$14,AGGREGATE(15,6,ROW(A$3:A$14)-ROW($A$2)/(MMULT(ISNUMBER(MATCH(B$2:G$2,D$17,0))*(B$3:G$14=D$16),{1;1;1;1;1;1})>0),ROWS($1:1))),"")

Regards
Bosco

 

[deciog]

Wow !!, it worked perfectly show, fantastic

Many thanks, Bosco.

Decio

 

[Peter Bartholomew]

= IF( k<=COUNT(matches), INDEX( timeSlot, SMALL( matches, k ) ), "" )
where matches refers to
= IF( INDEX(course, , WEEKDAY(day, 11 ) ) = experience, k )

 

[deciog]

Peter Bartholomew, I like the way you use name management, your formula works, it's fantastic

Thank you very much

Decio

 

[bosco_yip]

Another option in adopt Hui's idea.

In D18, copied down :

=IFERROR(INDEX(A$3:A$14,AGGREGATE(15,6,ROW(A$3:A$14)-ROW(A$2)/(INDEX(B$3:G$14,0,MATCH(D$17,B$2:G$2,0))=D$16),ROWS($1:1))),"")

Regards
Bosco

 

[deciog]

Bosco, very good too, worked perfectly

Thank you very much

Decio

 

[deciog]

Hui, everything in order

After some time I was able to make your formula give the result, check, thanks for the help given

=IF(COUNTIF(OFFSET($A$2:$A$14,,MATCH($D$17,$B$2:$G$2,0)),$D$16) < ROWS($D$18:D18), "", INDEX($A$2:$A$14, SMALL( IF(OFFSET($A$2:$A$14,,MATCH($D$17,$B$2:$G$2,0)) =$D$16, ROW( OFFSET($A$2:$A$14,,MATCH($D$17,$B$2:$G$2,0)))-1), ROW(A1))))

Decio