I have a worksheet as follows.

I'm looking for a formula at E4 that

• contains D5 (to specify from where the returned spilled range starts)
• returns a spilled range that starts from D5 and ends at the last non-empty cell below D5, which is G given the current input.

Does anyone know how to achieve this?

CodePudding user response：

In E4:

=D5:INDEX(D:D,MATCH("zzz",D:D))&""

With Office 365 and XLOOKUP:

=D5:XLOOKUP("*",D:D,D:D,"",2,-1)&""

The two above will find any cell that returns an empty string "". So if in D14, there is ="" both above will include through D14.

If that is undesirable we can use this formula, that works for any Excel since 2010:

=D5:INDEX(D:D,AGGREGATE(14,7,ROW(D:D)/(D:D<>""),1))&""

One note is that this will be slightly slower than either of the above methods.

CodePudding user response：

For mixed datatypes, try:

=D5:INDEX(D:D,MAX(MATCH(CHOOSE({1,2},99^99,"ω"),D:D)))&""

which will be many times faster than something such as

D5:INDEX(D:D,AGGREGATE(14,7,ROW(D:D)/(D:D<>""),1))&""

the latter being forced to iterate over more than 2 millions cells, even if there is only data as far as row 100, for example.