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