How to LOOKUP second from last cell not equal to 0, and return header value

0
votes

The excel formula:

LOOKUP(2,1/(G698:AW698<>0),G3:AW3)

works perfectly in finding the last cell in a row that is not 0 and returning it's header value.

How do I apply the same theory to find the 2nd from last, and then 3rd from last value for non-zero cells?

1
excellookup

1 Answers

1
votes

You would need to switch to INDEX/AGGREGATE

=INDEX($3:$3,AGGREGATE(14,6,COLUMN(G698:AW698)/((G698:AW698<>0)*(G698:AW698<>"")),2))

The 2 at the end is what returns the second to last.

Change the 2 at the end to 3 to get the third and so on.