Index and Match

[SeekingExcellence]

Hello Everyone,

Instructors Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9
Jan 13 Jan 20 Jan 27 Feb 3 Feb 10 Feb 17 Feb 24 Mar 3 Mar 10
Joe
Jeff Away
Tony Away
Sandy Away


So it didnt copy over very nicely, but I am trying to create a spreadsheet to track staff absences for weekend programming. You would have the staff on the left, Week at the top. Then if a person says they will be away for weeks 3 and 6 I can find their name, and using a drop down menu select "Away" for those weeks.

My hope is then to have another tab where I can use some sort of Index / Match formula to automatically list who will be away. I would love to be able to Select the "Week" from a drop down list and have the staff who are marked as "Away" for that week auto populated below it.

Any help would be much appreciated. I have been trying to figure this out for so long. First time posting in one of these forums, I am really stuck!

Thank you

[63falcondude]

Hello and welcome to the forum.

With your weeks in B1:J1, dates in B2:J2, names in A3:A6, and status in B3:J6, all in Sheet1

and your drop-down in A2 of Sheet2, put this formula into B2 of Sheet2 and drag down as far as needed:

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF((Sheet1!B$1:J$1=A$2)*(Sheet1!B$3:J$6="Away"),ROW(Sheet1!A$3:A$6)),ROWS($1:1))),"") Ctrl Shift Enter

[tangangtanga]

Could you please post a sample workbook for this.

Thank you

[PaulM100]

Something Like this?

L5: =IFERROR(HLOOKUP($L$6,$B$1:$J$2,2,0),"")
Array formula in M6, drop down: =IFERROR(INDEX($A:$A,SMALL(IF((B$1:J$1=$L$6)*(B$3:J$6=$L$7),ROW($A$3:$A$6)),ROWS($1:1))),"")

[SeekingExcellence]

Hello,

Thanks everyone for the fast replies. I wasn’t expecting submissions so quickly. I’m at another job now, I’ll input the formula tonight and post a sample workbook.

Thank you!

[SeekingExcellence]

Hey,

Just wanted to say thank you. I dropped in the formula and adjusted the cell references and it worked! I wasn't far off in my attempts, but would have never pieced it together on my own.

[63falcondude]

Glad we could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.