CODEWITHSUNDEEP
excel
user3461432
user3461432

Reputation: 23

Select cell above last cell of column

I want to automatically select a cell 12 rows higher than the last cell of data in a column. I used the tips in this thread (excel function to find last number in column) to automatically select the last cell of data using Lookup(9.99E+307,A1:A1000), but I am having trouble selecting the cell 12 rows above that. Would ADDRESS work for this? Any help is appreciated.

Upvotes: 2

Views: 157

Answers (1)

Jerry
Jerry

Reputation: 71578

You can use INDEX and MATCH:

=INDEX(A1:A1000, MATCH(9^99, A1:A1000)-12)

MATCH works a little like LOOKUP, but instead of returning a value, it returns the row number where there was the last number. INDEX picks this row number up and returns the value in that particular row, minus 12.

Upvotes: 2

Related Questions