Search code examples
google-sheetsformulas

Getting the second, third, fourth value from a table

I'm trying to calculate the 1st, 2nd and 3rd, 4th-year revenues of order from a table I have.

I'm created a formula in Google Sheets to return the first non-zero value in a table:

=ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(3:3,,SMALL(IF(J3:R3<>0,COLUMN(J3:R3),""),1))), 1, 1)

This does the job I need to but I'm not sure how to adapt the formula to get to the second or third values instead of the first.

Can anyone point me in the right direction?

Solution

  • You should just be able to change the first 1 to 2, 3 etc.

    =ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(3:3,,SMALL(IF(J3:R3<>0,COLUMN(J3:R3),""),2))), 1, 1)

    It works for me without the array formula wrapping

    =INDEX(3:3,,SMALL(IF(J3:R3<>0,COLUMN(J3:R3),""),2))

    BTW if you do it like this you can pull it across to get the first, second, third etc.

    =INDEX(3:3,,SMALL(IF($J3:$R3<>0,COLUMN($J3:$R3),""),columns($A:A)))