Lookup row number based on multiple criteria - Excel

user_3683930 0
Viewed 1542

I am trying to work out how to select the correct dept from the following range for a given person on a given day

enter image description here

In cell H2 I would like to add a formula that can be dragged down that will return which department that person was in on that date.

I have managed to work out how to do this with an exact match...

 {=INDEX(C2:C6,MATCH(1,(F2=D2:D6)*(G2=A2:A5),0))}

The above array formula works if the start date is exactly the same as the report date but I am struggling to figure out how to amend this to find where the report date is between the start date and end date. Can anyone help with this?

excel-formula excel
1 Answers

Untested, but try

=INDEX(C2:C6,MATCH(1,(F2=D2:D6)*(G2>=A2:A5)*(G2<=B2:B5),0))

entered as an array formula.

user_70080440
Related