Forum Discussion
Return value based on multiple critieria
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.
Yes you are correct I incorrectly assigned the responses. Expected result should be:
| Pers.No. | 9-Feb-20 | 12-Feb-20 | 3-Nov-20 |
| 10007 | 5N040001 | 5A030001 | 5A030001 |
| 10055 | 5N010001 | 5N010001 | 5A030001 |
I appreciate your pickup and assistance
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.