CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
nn123
nn123

Reputation: 33

Get last row index for a specific number in Google Sheets

I have a column consisting of single numbers and blank spaces. The numbers are not unique, but can occur in multiple cells. I want to find the row index for the last number 9 (i.e., in the photo above it is in row 12). What formula can I use for this purpose? I cant get it to work with MATCH or INDEX.

example image of the column of numbers and empty spaces

Upvotes: 0

Views: 414

Answers (3)

basic
basic

Reputation: 11968

Use LOOKUP which lets you search on a column a specific key (in your case C1, to return the last value 1/(A:A=C1) is used) and returns a specific range of matches (in your case to return the row index the formula takes use of ROW):

=ArrayFormula(LOOKUP(1,1/(A:A=C1),ROW(A:A)))

enter image description here

Upvotes: 2

JvdV
JvdV

Reputation: 75840

You could try:

=INDEX(MAX(ROW(A:A)*(A:A=9)),)

Upvotes: 1

Marios
Marios

Reputation: 27348

Try this:

=INDEX(FILTER(ROW(A1:A),A1:A=9),COUNTIF(A1:A,9))

enter image description here

Upvotes: 1

Related Questions