Array function is not working anymore

Question

Dear allI 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...)&nbsp;&nbsp;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.&nbsp;I also tried to do this on Excel online, but there I cannot insert array functions.&nbsp;Here a link to an example file:https://www.herber.de/bbs/user/153111.xlsx

riny_van_eekelen · Answer

StefanMeier&nbsp;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.

stefanmeier · Answer

Thanks a lot, your solution is working perfect for me!Even my formula is working in your sheet, but why?

riny_van_eekelen · Answer

StefanMeier&nbsp;Can't really answer why. Sorry?

stefanmeier · Answer

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.

sergeibaklan · Answer

StefanMeier&nbsp;
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.&nbsp; If drag it down it'll be -ROW(A2), -ROW(A3), etc and you have correct result.
&nbsp;
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.
&nbsp;
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.
&nbsp;
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.

Forum Discussion

Array function is not working anymore

Share

Resources