Array function is not working anymore

[StefanMeier]

Dear all

I am using an array function that work so far, now it is not anymore, to be more precise, it is working in excel sheets that I made last year, using the same formula in the same excel sheet does not work anymore.

The formula is:

=INDEX($B$2:$B$11;KGRÖSSTE(($A$2:$A$11=$F$1)*(ZEILE($A$2:$A$11)-1);ZÄHLENWENN($A$2:$A$11;$F$1)+1-ZEILE(A1)))

(it's in german i know...)

I am working on a company computer. Maybe some settings were changed by company policy, I don't know, So far nobody was able to help me.

I also tried to do this on Excel online, but there I cannot insert array functions.

Here a link to an example file:

https://www.herber.de/bbs/user/153111.xlsx

[#2]

@StefanMeier See if the attached file works for you. In E4:F7 I used your formula, but since you tagged your post with Office365, you may want to use the Filter function as demonstrated in cell I4.

[#3]

Thanks a lot, your solution is working perfect for me!

Even my formula is working in your sheet, but why?

[#4]

@StefanMeier Can't really answer why. Sorry?

[#5]

@Riny_van_Eekelen thanks a lot anyway , big help, it is really weird... Your sheet is now working with my formula as long I do not change the amount of cells.

[#6]

@StefanMeier 

In your formula for all cells in the last part you use -ROW(A1). It looks like you re-enter formula, not drag it to the down.  If drag it down it'll be -ROW(A2), -ROW(A3), etc and you have correct result.

As another comment, even in pre-DA Excel it's better to avoid array formulae, i.e. entered with CSE, and use functions which natively work with arrays. In this case that could be AGGREGATE, for example

=INDEX($B$2:$B$11,  AGGREGATE(14,6, 1/($A$2:$A$11=$F$1)*(ROW($A$2:$A$11)-1),COUNTIF($A$2:$A$11,$F$1)+1-ROW(A1)))

doesn't require CSE and returns correct result.

In Excel with dynamic arrays formula with CSE returns first element of the array. You may check on =SEQUENCE(5) entered with Ctrl+Shift+Enter, it returns only one value with 1.

Have no pre-DA Excel to test previous behaviour, perhaps I could be incorrect in details regarding first comment. But the point is the same - avoid CSE in old Excel.