Massive lease schedule workbook returning lease starting and termination dates

Hi everyone! I hope you are well 🙂 I am an auditor and was wondering if someone could please help me solve the following problem that Im facing when dealing with my clients lease schedules...

excel

Formulas and Functions

training

OliverScheurich
Mar 17, 2023
AuditorSB
=INDEX($A$3:$A$16,SMALL(IF(B$3:B$16<>"",ROW($B$3:$B$16)-2),1))
You can try this formula for the first lease. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=INDEX($A$3:$A$16,LARGE(IF(B$3:B$16<>"",ROW($B$3:$B$16)-2),1))
This is the formula for the last lease in the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
The formulas are in cells B18 and B19 and filled across ranges B18:F18 and B19:F19.

OliverScheurich

Gold Contributor

Mar 17, 2023

AuditorSB

=INDEX($A$3:$A$16,SMALL(IF(B$3:B$16<>"",ROW($B$3:$B$16)-2),1))

You can try this formula for the first lease. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

=INDEX($A$3:$A$16,LARGE(IF(B$3:B$16<>"",ROW($B$3:$B$16)-2),1))

This is the formula for the last lease in the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

The formulas are in cells B18 and B19 and filled across ranges B18:F18 and B19:F19.

AuditorSB
Copper Contributor
Mar 17, 2023
OliverScheurich WOW! Thank you so much for your assistance! This initially worked and I am applying it to the whole population now and hopefully it will work there as well 🙂

Forum Discussion

Massive lease schedule workbook returning lease starting and termination dates

Resources