Jun 13 2022 11:17 AM
Jun 13 2022 12:40 PM
@jlopez31 When I enter that formula in an empty sheet I just get several NUM errors and mostly N/A as you didn't provide the context in which you use the formula. What's in those reference you use?
So, difficult to say without the file on hand, but try this, and copy down as far as needed.
Otherwise, you can perhaps share a file via Onedrive or something similar.
Jun 13 2022 03:05 PM
Whenever you gave legacy Excel an array where it was expecting a value, it cut the array down to a single value that happened to be on the same row using 'implicit intersection'. That can be emulated in modern Excel but preceding the array with an '@' as @Riny_van_Eekelen suggested. That is not usually the best solution though, it is better to write a formula to take advantage of array behaviour.
With the formula you provided, I suspect the initial check of over 2000 values is serving very little purpose Your formula is capable of returning 5 meaningful values and the purpose of having a further 2000 zeros or #N/A is not clear.