Need help with INDEX-MATCH function

[dreddster]

Hey Guys,

I have the following table:

On HR tab, for each week an employee is assigned to work, on row 3, 6, 9 etc.the short name of the project is indexed.

And the formula is:
=INDEX(Dept1!$C$4:$C$7;
(MATCH(1;($A3=Dept1!$E$4:$E$7)
*(E$2>=Dept1!$I$4:$I$7)
*(E$2<=Dept1!$J$4:$J$7);0)))

However this is only for 1 spot for employees, which is column E. My question is, how do I make it to check for column F, G and H as well?

I tried with OR, like:

=INDEX(Dept1!$C$4:$C$7;
(MATCH(1;($A3=OR(Dept1!$E$4:$E$7; Dept1!$F$4:$F$7; Dept1!$G$4:$G$7; Dept1!$H$4:$H$7))
*(E$2>=Dept1!$I$4:$I$7)
*(E$2<=Dept1!$J$4:$J$7);0)))

and

=INDEX(Dept1!$C$4:$C$7;
OR(MATCH(1;($A3=Dept1!$E$4:$E$7)
*(E$2>=Dept1!$I$4:$I$7)
*(E$2<=Dept1!$J$4:$J$7);0)));
(MATCH(1;($A3=Dept1!$F$4:$F$7)
*(E$2>=Dept1!$I$4:$I$7)
*(E$2<=Dept1!$J$4:$J$7);0)))))
etc.,

But it doesn't work. I don't know how to set it up properly with these array functions.

Any ideas? Thank you so much!

P.S.: Moreover I have to make it check on all 5 sheets, ex.Dept1 to Dept5.

[dreddster]

I created

=INDEX(Dept1!$C$4:$C$7; OR(MATCH(1;($A3=Dept1!$E$4:$E$7)*(E$2>=Dept1!$I$4:$I$7)*(E$2<=Dept1!$J$4:$J$7);0); MATCH(1;($A3=Dept1!$F$4:$F$7)*(E$2>=Dept1!$I$4:$I$7)*(E$2<=Dept1!$J$4:$J$7);0)))

but it works only if a name is on both E and F....as if I used AND instead of OR.

[dreddster]

=INDEX(Dept1!$C$4:$C$7; MATCH(1;($A3=Dept1!$E$4:$E$7&Dept1!$F$4:$F$7&Dept1!$G$4:$G$7&Dept1!$H$4:$H$7)*(E$2>=Dept1!$I$4:$I$7)*(E$2<=Dept1!$J$4:$J$7);0))

hmm, that seems to be working! Any idea how to do that for 5 sheets (dept1 to dept5)?