Get last row index for a specific number in Google Sheets

0
votes

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

3
google-sheetsgoogle-sheets-formula

3 Answers

2
votes

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

1
votes

Try this:

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

enter image description here

1
votes

You could try:

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