0

I'm looking for a formula to find the most recent nonblank row, to compute some additional statistics on. My spreadsheet is going to look something like this:

A B C D
1 1 3
2 2 5 2 5
3 3 7
4 4 9
5 5 11
6 6 13 4 8
7 7 15

For example, for the formula in cell C6, I want it to discover that the most recent row with data in column C was row 2, and make a computation based on that. I will not know, in advance, how many blank rows there are in between.

It looks like the XMATCH function would do just what I want, but that appears to be Office 365 only. I'm using Excel 2016 for Mac, and I'd like my spreadsheet to be portable to Excel versions of that vintage.

Willing to use VBA if I have to, I guess.

Improve this question

1 Answer 1

Reset to default
1

You should be able to do it with a LOOKUP, like this:

=LOOKUP(2,1/(C1:C6<>""),C1:C6)

Another method (way less clean, but it works...):

=INDIRECT("C"&SUMPRODUCT(MAX(ROW(C1:C5)*(C1:C5<>""))))

Those will return you the value of C2 (in this case.)

Improve this answer
2
  • Oh, my goodness. No, not exactly "clean", but you're right, it works! If nothing better turns up, I guess this'll be my solution. Thanks.
    – Steve Summit
    Commented Dec 20, 2022 at 16:54
  • I added a better option
    – Yisroel Tech
    Commented Dec 21, 2022 at 14:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .