Forum Discussion
Return value based on multiple critieria
Hi,
I'm trying to return the WSR rule (Work Schedule Roster) that applied to an individual on a given date. The first table is my expected result for the given values in the data table.
| Pers.No. | 9-Feb-20 | 12-Feb-20 | 3-Nov-20 | ||
| 10007 | 5N040001 | 5A030001 | 5A030001 | ||
| 10055 | 5N010001 | 5N010001 | 5A030001 | ||
| Data table | |||||
| Pers.No. | Last name | First name | WS rule | Start Date | End Date |
| 9994 | Brown | Mary | 5A040001 | 9-Jan-12 | 31-Dec-99 |
| 9998 | Jones | Phillip | 5D220001 | 1-Apr-18 | 31-Dec-99 |
| 10007 | Obama | Tracey | 5N040001 | 6-Jun-11 | 10-Feb-20 |
| 10007 | Obama | Tracey | 5A030001 | 11-Feb-20 | 31-Dec-99 |
| 10055 | Chang | Ben | 5N010001 | 18-Apr-16 | 2-Oct-20 |
| 10055 | Chang | Ben | 5A030001 | 2-Nov-20 | 31-Dec-99 |
DPifarre Please see the attached file. Your data will stay in a table named Data on Tab Data and the result of look up will be on Tab Output. Note that I have corrected your End date to the year 9999 instead of 1999 as currently.
I use Index/Match array formula to look up. Basically, it will look at the Data Table, try to find the intersection of column 4 where your WS Rule stays and the row where all conditions of (Person ID, Start date <= your lookup date, End date >= your look up date) are satisfied.
14 Replies
DPifarre Why 10007 with the date of 12 Feb shows 5N040001 in your expected result? Should it show 5A030001 instead because 12 Feb belongs to the duration starting on 11 Feb?
Similarly, 10055 with the date of 12 Feb showing 5A030001 in your expected result. Should it show 5N010001 instead because 12 Feb belongs to the duration ending on 2 Oct?
If you could confirm your rule, the formula to pull the desired result would not be a problem.
DPifarre Please see the attached file. Your data will stay in a table named Data on Tab Data and the result of look up will be on Tab Output. Note that I have corrected your End date to the year 9999 instead of 1999 as currently.
I use Index/Match array formula to look up. Basically, it will look at the Data Table, try to find the intersection of column 4 where your WS Rule stays and the row where all conditions of (Person ID, Start date <= your lookup date, End date >= your look up date) are satisfied.
