find first non-blank text cell based on multiple criteria (w/o array)

[Villalobos]

Hello,

I would need your support to find the first non-blank text cell based on multiple criteria but without array formula. The original file has more 10.000 rows and the calculation time of my array formula takes long time.

Please, see the details and expected result in the sample file.

Do you have any idea?

Thank you in advance for your feedback.

 

[deannaed]

Hello


In I12 Please try:

=INDEX(F8:F2000,MATCH(TRUE,INDEX((LEN(F8:F2000)<>0),0),0))


I hope this helps

 

[GraH - Guido]

Hi,

I copied your data down to have 225K rows and played around with some data.
This one seems to calculate in a blink of an eye.
=IFERROR(INDEX($F$8:$F$550000,AGGREGATE(15,6,(ROW($A$8:$A$550000)-7)/((($B$8:$B$550000)=$I$8)*(($C$8:$C$550000)=$I$9)*(($D$8:$D$550000)=$I$10)*(($E$8:$E$550000)=$I$11)*(NOT(ISBLANK($F$8:$F$550000)))),1)),"")

Please verify if it is still working with your real life data.

 

[Villalobos]

Hi GraH - Guido,

I checked your formula but doesn't work on my side.

 

[bosco_yip]

Hi GraH - Guido,

I checked your formula but doesn't work on my side.

Maybe,

Changed your formula in I14 from :

=IFERROR(INDEX($F$8:$F$550000,AGGREGATE(15,6,(ROW($A$8:$A$550000)-7)/((($B$8:$B$550000)=$I$8)*(($C$8:$C$550000)=$I$9)*(($D$8:$D$550000)=$I$10)*(($E$8:$E$550000)=$I$11)*(NOT(ISBLANK($F$8:$F$550000)))),1)),"")

Into this :

=IFERROR(INDEX($F$8:$F$550000,AGGREGATE(15,6,ROW($A$8:$A$550000)-7/(($B$8:$B$550000=$I$8)*($C$8:$C$550000=$I$9)*($D$8:$D$550000=$I$10)*($E$8:$E$550000=$I$11)*($F$8:$F$550000<>"")),1)),"")

Regards
Bosco

 

[Villalobos]

Thank you, it works well.

 

[Peter Bartholomew]

Why not a helper field?

= IF( AND( (Company=[@Company]),
(Platform=[@Platform]),
(Region=[@Region]),
(Exclusion_check=[@[Exclusion check]]),
([@[Supply info]]<>"") ),
RowNumber )

with the result cell

= INDEX( Table1[Supply info], MIN( Table1[RecordNumber] ) )

MINIFS would offer another possibility for obtaining a result without a helper range and is several times faster than an array formula.

Really though, if speed is so important that you have to forego clarity, then it might be better to use compiled code rather than spreadsheets. Even machine code if one gets desperate!