CODEWITHSUNDEEP
excelindexingmatch
Rock1432
Rock1432

Reputation: 209

Using MATCH function to pull out index of last non-zero cell in a row

I have a row with cells of the following format.

enter image description here

I would like a formula which would pull out the index of the last non-zero cell. E.g. I1.

I have the following formula:

=INDEX(A1:N1<>0,0)

This seems to work for a number of my examples for other will just return the length of the row.

Thanks

Upvotes: 0

Views: 1383

Answers (1)

Zack Barresse
Zack Barresse

Reputation: 249

To get the index of the last value you can use...

=LOOKUP(2,1/(A1:N1<>0),COLUMN(A1:N1))

To return the value of the last found non-zero (index) you can combine with INDEX to become...

=INDEX(A1:N1,,LOOKUP(2,1/(A1:N1<>0),COLUMN(A1:N1)))

Upvotes: 2

Related Questions