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.
SergeiBaklan 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.
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 SergeiBaklan:
=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 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.
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 , 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.
