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

J.C. Benny

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.

Matt Lynn-PCG

@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.

Matt Lynn-PCG

@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.

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!