Errata....

On Jan 11, 11:02*am, I wrote:
I would expect that MATCH() uses a binary search algorithm

No reason for me to make that assumption, at in this particular case.
I would hope that MATCH() uses a binary search (or better) for large
ranges. But for small ranges, a linear search would do just fine. I
did not pay any attention to the relative values of the __two__
nearest nonblank cells. If MATCH() happens to search backwards
linearly (at least for small ranges) and if the nearest nonblank just
happened to be larger than the 2nd nearest nonblank, Lori's MATCH()
would appear to work, but only by coincidence.

However, Lori's MATCH() always seems to work (i.e. it finds the
nearest nonblank value) even if the 2nd nearest nonblank cell is
larger than the nearest.

I am still curious why it works.

However, Lori's structure of the SUMPRODUCT() does make it very clear
how I would design my own VBA function to find the nearest nonblank
cell and return the row number. Thanks again for that.

But I am still interested in a non-VBA solution. If Lori's MATCH()
solution is it, I would still appreciate an explanation, in light of
the help page description (that is, my understanding of it).

Thanks again, Lori, for a very clean approach, if not the solution.


, which normally
starts with the middle element. *But unpredictable things happen in a binary
search if the lookup-array is not sorted. *Nonetheless, I would be surprised
that it always determines that the last non-zero element of the array meets
the criteria.)

Thanks for any insight.

That said, I do like your structure of the SUMPRODUCT(), performing the
look-up only once. *So if MATCH() does not really work in the final analysis,
I believe I am looking for a look-up function that simply searches backwards
sequentially -- or some other clever usage that has that effect.

Thanks again.