I need help creating a formula that will respond with benefit information that matches an ID number and falls between two dates. For example, here would be the reference chart:
<tbody>
</tbody>
And this is what the calculation results should look like:
<tbody>
</tbody>
All my previous attempts seem to return the first value in the reference table for all the cells.
Help much appreciated!
Employee ID # | Start Date | End Date | Benefit Type |
101 | 7/1/14 | 8/31/14 | None |
101 | 9/1/14 | Employee Only | |
102 | 7/1/14 | Employee +1 | |
103 | 7/1/14 | None |
<tbody>
</tbody>
And this is what the calculation results should look like:
Employee ID# | Pay Date | Benefit Type |
101 | 9/30/14 | Employee Only |
102 | 9/30/14 | Employee +1 |
103 | 9/30/14 | None |
<tbody>
</tbody>
All my previous attempts seem to return the first value in the reference table for all the cells.
Help much appreciated!