SOLVED

# selecting data from variable row length

Occasional Contributor

# 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?

6 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: selecting data from variable row length

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)

# Re: selecting data from variable row length

Omg, it works. Thank you very much.

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

# Re: selecting data from variable row length

By visiting a forum such as this one [grin]

indeed indeed

# Re: selecting data from variable row length

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?

# Re: selecting data from variable row length

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