06-03-2020 03:26 PM - edited 06-04-2020 01:26 PM
06-03-2020 03:26 PM - edited 06-04-2020 01:26 PM
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.||Last name||First name||WS rule||Start Date||End Date|
06-03-2020 05:46 PM
@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.
06-04-2020 06:20 PMSolution
@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.
06-05-2020 06:39 PM
@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.
06-06-2020 03:21 AM
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.
06-06-2020 08:24 AM
@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.
06-06-2020 11:25 AM - edited 06-06-2020 11:27 AM
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:
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.
06-06-2020 11:47 AM
@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.
06-06-2020 12:00 PM - edited 06-06-2020 12:06 PM
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:
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.