Lookup value in column from bottom to top

What does "position from the bottom" mean? Do you want to return the row number where the last "" occurs (let's say this is on row 7), or the number of rows above the bottom (i.e. 10 - 7 = 3) ?

Pete

By the way, this array* formula will give you the row number where the last "" appears (i.e. closest to the bottom):

=MAX(IF(A1:A10="",ROW(A1:A10)))

*An array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter.

You may need to use a semicolon ( ; ) instead of a comma ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

Thank you, it seems to work. I will place this formula next to the bottom cell in the range I search, so I will add =ROW()+1-MAX(IF(AO14:AO23="";ROW(AO14:AO23))).

I hope the array format doesn't cause any problems, but I am not that familiar with these kind of formulas. In this spreadsheet the formula will be repeated close to 30 000 times, and will also contain an INDIRECT() formula to specify the range if needed. I might even need to include an IF() formula to exclude the case where there is no match.

But now I have something to work with! Thank you!

It just returns zero if there is no match.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hello again,

It seems this isn't really solved after all. The formula that was provided does work. But it is not practical in the worksheet given that it must be repeated thousands of times, and that it has to be activated by ctrl + shift + Enter in each cell.

I have a data set of about 30 000 rows. This formula will check the ten previous rows as described before, and then the next formula will do the same one row further down and so on.


1) Is there a way to activate several array formulas at once?

2) Are there any alternative formulas to solve the original problem?

Never mind. I think I figured it out, and I will use this formula:

=ROW()-LOOKUP(2;1/(AO14:AO23="");ROW(AO14:AO23))

No arrays, and it seem to work ok.