Index Match with 3 Criteria

Hi there,

I have a holiday calendar for work where I want the days of the week of the calendar to show whether the employee will be here or not. I have attached a document to give you some idea of what's going on, so on the 'leave dates' sheet is where you go to enter periods when people will be away, and then on the 'calendar' sheet it should display the corresponding text of whether the employee will be away or not for certain dates. So the formula in the day cells needs to check 3 criteria:

1) do a match on the employee name
2) check whether the given calendar date is greater than or equal to the start date of the leave
3) check whether the given calendar date is less than or equal to the finish date of the leave

As you can see I have tried an index match array formula and it's just not working for me:

Please Login or Register  to view this content.

It's important the formula brings back the status column value (column D). Thanks so much for your help in advance it is much appreciated!


example-index-match.xlsx

Put in B3:

=IFERROR(INDEX('leave dates'!$D$2:$D$4,MATCH(1,('leave dates'!$A$2:$A$4=$A3)*(B$1>='leave dates'!$B$2:$B$4)*(B$1<='leave dates'!$C$2:$C$4),0)),"")

and then press CTRL-SHIFT-ENTER, and then copied cross and down

this worked awesome, thanks so much!

Thanks Tony and TMS!