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.
As a comment, from my point of view it's better to avoid array formula where possible
=INDEX(Data!$D:$D,MATCH(1,INDEX( (Data!$E:$E<=B$1)*(Data!$F:$F>=B$1)*(Data!$A:$A=$A2),0),0))SergeiBaklan Thanks for your suggestion.
Do you know why your formulas turn into array formulas when the workbook is opened as in the pic? And for unknown reason, whenever I evaluated your formula, my Excel became not responding and I had to restart the application. I even tried to remove the array brackets then evaluate it but my Excel kept freezing. I do not run into this problem when evaluating my array formulas. Just wonder what causes the issue since I used to think array formulas are slower but my google search shows some tests indicating they actually take memory and calculate faster.
