Forum Discussion
Array function is not working anymore
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...
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.
Thanks a lot, your solution is working perfect for me!
Even my formula is working in your sheet, but why?
Even my formula is working in your sheet, but why?
StefanMeier Can't really answer why. Sorry?
- 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.
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.
