SOLVED

Highlighted
New Contributor

# Return value based on multiple critieria

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-20 12-Feb-20 3-Nov-20 10007 5N040001 5A030001 5A030001 10055 5N010001 5N010001 5A030001 Data table Pers.No. Last name First name WS rule Start Date End Date 9994 Brown Mary 5A040001 9-Jan-12 31-Dec-99 9998 Jones Phillip 5D220001 1-Apr-18 31-Dec-99 10007 Obama Tracey 5N040001 6-Jun-11 10-Feb-20 10007 Obama Tracey 5A030001 11-Feb-20 31-Dec-99 10055 Chang Ben 5N010001 18-Apr-16 2-Oct-20 10055 Chang Ben 5A030001 2-Nov-20 31-Dec-99
14 Replies
Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted
Best Response confirmed by DPifarre (New Contributor)
Solution

# Re: 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.

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

# Re: Return value based on multiple critieria

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.

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

# Re: Return value based on multiple critieria

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

Highlighted

You're welcome!

Highlighted

# Re: Return value based on multiple critieria

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