CODEWITHSUNDEEP
excel
Alon_T
Alon_T

Reputation: 1440

Finding the row number of last row in column with a specific value in excel

So I have a column with values, and I am looking for the highest/latest row number with a specific value. An example:

enter image description here

In this case, I want a function that when searching for the last row number, within the value range, containing 0, it would return 8.

Another option is to get the index of the first empty cell and subtract 1.

Thanks

Upvotes: 0

Views: 4014

Answers (3)

BrakNicku
BrakNicku

Reputation: 5990

To find row number of last 0 in a range you can use:

=LOOKUP(2,1/(A1:A11=0),ROW(A1:A11))

Edit: To exclude empty cells from result:

=LOOKUP(2,1/((A1:A11=0)*(A1:A11<>"")),ROW(A1:A11))

Upvotes: 2

Alon_T
Alon_T

Reputation: 1440

=MAX(IF(ISERROR(E897:E908),0,IF(E897:E908=0,ROW(E897:E908),0)))-ROW(E897)+1

CTRL+ENTER+SHIFT I just checked if the line doesn't contain an error and if it doesn't, if it has my value I take the highest row number and then, from that, I subtract the initial row number, and get 8.

Upvotes: 0

xidgel
xidgel

Reputation: 3145

Try this:

=MAX(IF(LookFor=LookIn,ROW(LookIn),0))

entered as an array formula CTRL-SHIFT-ENTER. LookFor is the value you're looking for, say 0, and LookIn is the range you're searching, say A1:A15.

Hope that helps

Upvotes: 1

Related Questions