Forum Discussion

jsrmenke's avatar
jsrmenke
Copper Contributor
Sep 08, 2025
Solved

Excel help!

How would I capture the data from the last cell in a column when the column length is variable?
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Sep 11, 2025

    Thanks to SergeiBaklan​ I may now understand what you want. To get the last displayed value in column B, use

    =INDEX(B1:B100, MAX(IF(($B$14:$B$100<>"")*(($D$14:$D$100>0)+($F$14:$F$100>0)), ROW($D$14:$D$100))))

    Adjust the column in B1:B100 for the last displayed value in other columns, for example for column K:

    =INDEX(K1:K100, MAX(IF(($B$14:$B$100<>"")*(($D$14:$D$100>0)+($F$14:$F$100>0)), ROW($D$14:$D$100))))

HansVogelaar's avatar
HansVogelaar
MVP
Sep 11, 2025

Thanks to SergeiBaklan​ I may now understand what you want. To get the last displayed value in column B, use

=INDEX(B1:B100, MAX(IF(($B$14:$B$100<>"")*(($D$14:$D$100>0)+($F$14:$F$100>0)), ROW($D$14:$D$100))))

Adjust the column in B1:B100 for the last displayed value in other columns, for example for column K:

=INDEX(K1:K100, MAX(IF(($B$14:$B$100<>"")*(($D$14:$D$100>0)+($F$14:$F$100>0)), ROW($D$14:$D$100))))

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 11, 2025

Or we may copy/paste conditional formatting rule

=XLOOKUP( 0, (PaymentSchedule3[Payment number]="")+(PaymentSchedule3[Beginning
balance]=0)*(PaymentSchedule3[Extra
payment]=0),  PaymentSchedule3[Payment number],,,-1 )

Resources