Help with Index/Match/If/Or

[zodiaceuk]

Hi Guys.

Hoping someone can help me with this, I can’t seem to figure it out.

Table Absence:
Employee ID
Date
Reason

Table RTW:
Employee ID
Absence start date
Absence end date
RTW completed date

(There is other data, not relevant)

If looking to match the absence to the RTW.
Each absence day is recorded, however, there will be only one RTW for the entire period (hope that make sense).

I’ve tried:

{=INDEX(RTW[Completed Date],MATCH(1,IF(Absence[@Employee ID]]=RTW[Employee ID],IF(OR(Absence[@Date]=RTW[First Absence Date],RTW[Last Absence Date]),1),0),0))

I can get the date to return, unfortunately, it returns the same date for any absences for that employee.... Have I overlooked something please?

Thanks in advance ?

 

[pbornemeier]

Please provide sample data for each table.
Please provide the expected value(s) for the data provided as well as the value(s) you are actually getting.