Array formula for lookup a max value cost too much time
 

Dear Excel friends,

Still struckling with a formula which cost a lot of time to run because of the amount of records (appr 50.000). When runs 'calculate' it cost many many minutes that I have to wait :(

I'm using below formula based on array (Ctrl+Shift Enter)

{=MAX(IF(I:I=I2;Y:Y))}

Because it reports a zero when nothing found in column Y it's changed to

{=IF(MAX(IF(I:I=I2;Y:Y))=0;"";MAX(IF(I:I=I2;Y:Y))) }

Both formulas spend too much time when calculate.
Because I'm using this type of formula also in other columns you understand that this way of doing it is a lot of 'calculation waiting time'.

Is there a faster way for this formula ? (Another formula or VBA code).

Regards, Johan

Array formula for lookup a max value cost too much time
 

Hi Johan,

Am Mon, 31 Aug 2020 19:31:04 -0700 (PDT) schrieb :

{=IF(MAX(IF(I:I=I2;Y:Y))=0;"";MAX(IF(I:I=I2;Y:Y))) }

Both formulas spend too much time when calculate.

I tried following formula with more than 50000 values and it did it in a
blink:
=IF(COUNTIF(I:I,I2)=0,"",MAXIFS(Y:Y,I:I,I2))


Regards
Claus B.
--
Windows10
Office 2016

Array formula for lookup a max value cost too much time
 

Amazing :)

=IF(MAXIFS(Y:Y;I:I;I2)=0;"";MAXIFS(Y:Y;I:I;I2))

9sec calculate time for 50.000 records.

THANKS !!

Array formula for lookup a max value cost too much time
 

Hi Johan,

Am Tue, 1 Sep 2020 10:45:14 -0700 (PDT) schrieb :

Amazing :)

=IF(MAXIFS(Y:Y;I:I;I2)=0;"";MAXIFS(Y:Y;I:I;I2))

9sec calculate time for 50.000 records.

if you change the first MAXIFS to COUNTIF it is a little bit faster:

=IF(COUNTIF(I:I,I2)=0,"",MAXIFS(Y:Y,I:I,I2))


Regards
Claus B.
--
Windows10
Office 2016

Array formula for lookup a max value cost too much time
 

Dear Claus,

Thanks remarked solution.
The problem is that when there is no value in column YY then it results with COUNTIF in 0 and with MAXIFS in "" (empty).

Is there also a formula solution for :)

regards, Johan

Array formula for lookup a max value cost too much time
 

Hi Johan,

Am Tue, 1 Sep 2020 20:35:39 -0700 (PDT) schrieb :

The problem is that when there is no value in column YY then it results with COUNTIF in 0 and with MAXIFS in "" (empty).

try:
=IF(COUNTIFS(I:I,I2,Y:Y,"<")=0,"",MAXIFS(Y:Y,I:I, I2))

Regards
Claus B.
--
Windows10
Office 2016