Forum Discussion
selecting data from variable row length
Hi,
I use data from row 4 last Cell (M4) and 5th last Cell (H4) entry.
But subsequent rows (5, 6, etc.) do not have equal data range.
What function can I enter in Cell B4,5,6,etc., to always find last Cell and 5th last Cell data?
In B4, if you have Microsoft 365 or Office 2021:
=LET(m,MATCH(9.99999999999999E+307,4:4),INDEX(4:4,m)/INDEX(4:4,m-4))
Fill down.
If you have an older version:
=INDEX(4:4,MATCH(9.99999999999999E+307,4:4))/INDEX(4:4,MATCH(9.99999999999999E+307,4:4)-4)
6 Replies
In B4, if you have Microsoft 365 or Office 2021:
=LET(m,MATCH(9.99999999999999E+307,4:4),INDEX(4:4,m)/INDEX(4:4,m-4))
Fill down.
If you have an older version:
=INDEX(4:4,MATCH(9.99999999999999E+307,4:4))/INDEX(4:4,MATCH(9.99999999999999E+307,4:4)-4)
- Omg, it works. Thank you very much.
How could a "normal" user ever find such solutions?? 🤷:male_sign:By visiting a forum such as this one [grin]
