SOLVED

Return value based on multiple critieria

Copper Contributor

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-2012-Feb-203-Nov-20 
 100075N0400015A0300015A030001 
 100555N0100015N0100015A030001 
Data table    
Pers.No.Last nameFirst nameWS ruleStart DateEnd Date
9994BrownMary5A0400019-Jan-1231-Dec-99
9998JonesPhillip5D2200011-Apr-1831-Dec-99
10007ObamaTracey5N0400016-Jun-1110-Feb-20
10007ObamaTracey5A03000111-Feb-2031-Dec-99
10055ChangBen5N01000118-Apr-162-Oct-20
10055ChangBen5A0300012-Nov-2031-Dec-99
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.

@hynguyen 

Yes you are correct I incorrectly assigned the responses. Expected result should be:

Pers.No.9-Feb-2012-Feb-203-Nov-20
100075N0400015A0300015A030001
100555N0100015N0100015A030001

 

I appreciate your pickup and assistance

best response confirmed by DPifarre (Copper Contributor)
Solution

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

@hynguyen 

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

 

@Sergei Baklan 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.MultipleConditionLookup.png

@hynguyen 

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.

@Sergei Baklan The formulas show as array one even when I enabled editing. I am using Excel 2019. The weird thing is that this morning I tried to evaluate your formula again, this time Excel took a whole 1 min then returned blank in the output field of the Evaluate formula screen and the Evaluate button turned into Restart instead.

@hynguyen 

I'm sorry that happened. Will check the file some later on non-DA Excel, right now have no idea what could be wrong

@hynguyen 

 

The technique of inserting one or more INDEX functions at certain positions within a formula so as to avoid pressing CTRL+SHIFT+ENTER does not at all mean that that formula is not processed as an array formula. If anything, the non-CSE INDEX construction is less efficient than the equivalent CSE one; after all, an extra function call is an extra function call...

 

Since such constructions are processed in exactly the same manner as the CSE version, the use of entire column references is to be avoided. The following formula, provided by @Sergei Baklan:

 

=INDEX(Data!$D:$D,MATCH(1,INDEX( (Data!$E:$E<=B$1)*(Data!$F:$F>=B$1)*(Data!$A:$A=$A2),0),0))

 

is having to process more than 3 million cells, an astonishing number for a single formula. And that's just for one instance of that formula; I can imagine that if you had just a few hundred or so of the above then your spreadsheet would practically come to a halt.

 

Reduce the upper row being referenced to a suitably low, though sufficient, value. Better still, use either table ranges (which automatically detect the last-used cell within the range) or else define your own dynamic ranges.

 

Regards

@Jos_Woolley , in theory you are correct. In practice such formulas never process 3 million cells, they stop on first match. Even with no match it works fast if major part of the cells is blank. At least on relatively new versions of Excel.

 

IMHO, for such cases dynamic ranges complicate the maintenance, but if people who will work with the file are familiar with them - yes, better to use.

@Sergei Baklan 

 

I'm afraid that's simply not correct. I'm not referring to MATCH; even before the formula arrives at the MATCH part it has to process this product of arrays:

 

(Data!$E:$E<=B$1)*(Data!$F:$F>=B$1)*(Data!$A:$A=$A2)

 

and I assure you that every single one of the 3,145,728 cells referenced within Data!$A:$A, Data!$E:$E and Data!$F:$F will be processed, irrespective of whether they are empty or beyond the last-used cell in those ranges and also independent of the MATCH function to which they are being passed.

 

Regards

@Jos_Woolley That explains why my Excel kept frozen when evaluating the formula. I also try to avoid referring a whole column/row as much as possible in any formula. Thanks for your explanation.

@hynguyen 

 

You're welcome!

@hynguyen 

Thank you that works great and you've expressed it simply.

1 best response

Accepted Solutions
best response confirmed by DPifarre (Copper Contributor)
Solution

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

View solution in original post