Why does my Index/Collect Formula stop working after 8 rows?

Hello,

my formula stops working after it gets through 8 rows. I have a picture below where I'm just testing out 30 rows but I need this to work for about 4000 rows, however I can't figure out why it gives me an error after awhile.

This is my formula.

=INDEX(COLLECT($ItemCategory$1:$ItemCategory$30, $[Date Order]$1:$[Date Order]$30, [Date Order]1), [Row ID]1)

A working formula will help me build out a larger report that I am working on.

Find more posts tagged with

Formulas

Accepted answers

Matt Lynn ACT

@J.C. Benny So the formula isn't working because of the index part of it. It's trying to collect data that match criteria and then indexing the index value/number within that collected data.

So on row 9 you're saying "Show me the Item Category, of the 9th row that is in date order 2024-5. If you count there's only 8 of those.

So either that's correct and you just need to wrap that formula in an iferror()… or your logic is wrong. Also I would advise changing your specific row references to @rows etc so you can make that a column formula… if you solve the logic.

All comments

Matt Lynn ACT

@J.C. Benny So the formula isn't working because of the index part of it. It's trying to collect data that match criteria and then indexing the index value/number within that collected data.

So on row 9 you're saying "Show me the Item Category, of the 9th row that is in date order 2024-5. If you count there's only 8 of those.

J.C. Benny

@Matt Lynn-PCG Thank you for explanation.

I somewhat understand what you are saying but I don't fully understand how best to fix it.

I'm working on some edits but not having much success.

Edit: I actually just wrapped in a simple iferror formula like you said instead of trying to do anything too complex and looks to work just fine, thanks!

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.