selecting data from variable row length

[drdree73]

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?

[#2 · Accepted solution]

@drdree73 

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)

[#3]

Omg, it works. Thank you very much.

How could a "normal" user ever find such solutions?? 🤷‍:male_sign:

[#4]

@drdree73 

By visiting a forum such as this one [grin]

[#5]

indeed indeed :face_with_tears_of_joy:

[#6]

Since applying the formula, my excel tabs/sheets have become hidden (most of the time). They are there, but invisible. Show tabs is on. Any idea?

[#7]

@drdree73 

No idea. Formulas have nothing to do with showing sheet tabs or not...