Index and Match formula issue

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.

Find more posts tagged with

Formulas

Accepted answers

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)

All comments

sharkasits

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

What row is your Month titles on?
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

The Month titles are in the row 31
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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.