Index Match not cycling through entire index. Formula not updating when values change...

Hello! I have 2 tables: a PTO/Overtime table and an employee Availability table/per employee on separate worksheets.

Ideally, I want to be able to append a new PTO or overtime record to the PTO_OT table with the following columns:

• Employee
• StartDate
• EndDate
• Hours

In the Availability table, I have 365 row records for daily availability. In the PTO/Overtime column, I want to index match against the PTO_OT Table and return the hours if the date is within the Start-End date range and the Employee name matches. I am trying the following formula:

=IFERROR(INDEX(PTO_OT[Hours],MATCH(1,(Availability_EmpA!A2=PTO_OT_Emp)*(Availability_EmpA!B2>=PTO_OT[StartDate])*(Availability_EmpA!B2<=PTO_OT[EndDate]),0)),0) --> cntrl+shift+enter

The issue I am having is that the first PTO match based on DATE is being returned to the first employee avail. worksheet, though the employee names do not match.
The second PTO record is being returned to the second employee avail worksheet though the employee names do not match... and so on

Also, I have 2 PTO records for the same employee. They should both be reflected in the same employee availability table.

I suspect your issue is the fact you're using PTO_OT_EMP as opposed to PTO_ET[Employee], the former is a named range tied to a different table object.

Further to the above, you might also consider switching to a SUMIFS approach, e.g.:

Formula:

Please Login or Register  to view this content.

Correcting the range name back to PTO_OT[Employee] corrected the issue. Thank you!