index match with multiple returns in alphabetical order

Hi.

You're querying 1048574 rows in an array formula??

I'm surprised just a dozen or so iterations of that formula doesn't crash your computer! Certainly adding in additional clauses to effect the alphabetical sort over that range will cause serious calculation issues.

Do you really have data extending to the very end row of the sheet?

Regards

But, within an array formula (just as with other array-processing functions which do not require CTRL+SHIFT+ENTER, e.g. SUMPRODUCT, AGGREGATE, etc.) you cannot arbitrarily reference as many rows as you wish with no detriment to performance.

Each row that you reference has to be calculated, whether beyond your last row containing data or not. So if, for example, you only have data extending as far as row 1000, then, by referencing an entire column's worth of rows, you are effectively forcing Excel to calculate more than one million rows than are actually necessary, resulting in an astonishingly resource-heavy formula. And that's just for one instance of that formula.

With some functions, e.g. COUNTIF(S)/SUMIF(S), you can get away with referencing entire columns with no detriment to performance, though that does not apply to array formulas.

I know you think you're "hedging your bets" by setting such an upper reference, but you're actually doing far more harm than good. Surely you can choose a suitably low, though sufficient, upper bound for the end row being referenced other than the very last row?

Regards

I chose an upper row reference of 10:

=IF($B$1=CHOOSE({1,2},Holiday),"Holiday",IF(ROWS(B9:B$9)<=$N$3,INDEX(Deviations!B$3:B$10,MATCH(SMALL(IF(Deviations!$A$3:$A$10=$B$1,IF(Deviations!$E$3:$E$10<>"no",IF(Deviations!$F$3:$F$10<>"yes",COUNTIF(Deviations!B$3:B$10,"<"&Deviations!B$3:B$10)))),ROWS(B9:B$9)),IF(Deviations!$A$3:$A$10=$B$1,IF(Deviations!$E$3:$E$10<>"no",IF(Deviations!$F$3:$F$10<>"yes",COUNTIF(Deviations!B$3:B$10,"<"&Deviations!B$3:B$10)))),0)),""))

By the way, how is the Named Range Holiday defined? I'm curious about your initial clause as I can't see how it could be syntactically correct, though I guess I'm missing something.

Regards

Originally Posted by mamachrissy1028

The named range "Holiday" is a list of the 2016 holidays. If the date entered into B1 is one of those dates it will return the value of "Holiday". This is just a safety feature so we don't plan stuff for Holidays as they are not working days.

Actually I believe that the only condition which will lead to that initial construction returning "Holiday" is if the very first cell within the Named Range Holiday is equal to the entry in B1. All other cells within that range are redundant as far as that construction is concerned.

Regards

I think I can help you on these new queries, but it would be far easier if I had a workbook to use.

It doesn't have to be your actual workbook; a greatly reduced, mocked-up one with dummy data will do, so long as you clearly outline your expected results.

Regards