Index and Match formula issue

Roland Schroder

Hi All,

My colleague and I are trying to find the first occurrence of non-zero in a table and then return the corresponding month.

Example table

The formula that we are trying to work with is below, but whatever we try we keep getting an error of #UNPARSEABLE

=IFERROR(INDEX(January31:December31, MATCH(True, INDEX(January2:December2> 0, ), 0)), "")

Any help would be appreciated.

Paul Newcome

Assuming the column names are also the month titles, you would use something along these lines...

=INDEX(COLLECT(Jan$31:Dec$31, Jan@row:Dec@row, @cell> 0), 1, 1)

sharkasits

@Roland Schroder I'd love to help. I have a few questions to help me understand what you are trying to do.

1. What row is your Month titles on?
2. Are you trying to get the first month that has a non-zero in any row, or are you trying to get a non-zero in a specific row?

Roland Schroder

Hi,

Thank you

1. The Month titles are in the row 31
2. Yes we are trying to find the first month that has a non-zero in a specific row.

Paul Newcome

Assuming the column names are also the month titles, you would use something along these lines...

=INDEX(COLLECT(Jan$31:Dec$31, Jan@row:Dec@row, @cell> 0), 1, 1)

Roland Schroder

Thank you Paul,

that worked a treat.

Paul Newcome

Happy to help. 👍️