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.
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.
That's strange, nothing new in such formula. Do you have the same if enable editing? And on which version of Excel you are.
As for array vs regular. With dynamic arrays introduced in Excel this question is gone, however, in pre-DA Excel array formulas are preferably to be used if the same results can't be achieved with regular formulas. IMHO, performance is not an issue for 90% of task where the Excel is used, you will see no difference on it independently on which formulas to use. If critical, that's a lot of factors to be taken into account, there is no one answer for all cases. More is here Excel performance: Tips for optimizing performance obstructions .
At the same time quite often people forget to use Ctrl+Shift+Enter and spent lot of time to find why formula doesn't work.
