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.
Best Answer
-
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)
Answers
-
@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?
-
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.
-
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)
-
Thank you Paul,
that worked a treat.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!