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.
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.
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.
