#NUM error in Array formula using Index, Small, Row and Rows

Hi,

I’m having an issue with a formula that works for 10 cells then stops. I have a sheet with questions, which are assigned to buckets, sub-categories and categories.

I’m trying to create a helper sheet where I can Select the Category then it populates a list of Sub-categories, which in turn populates the Buckets and lastly the Questions assigned to the buckets. Everything works for the first 10 rows then stops generating the Bucket answers. When I view the formula it’s returning a #NUM error and I can’t for the life of me figure out why. I've highlighted the cell in yellow (on the Formula tab) where it stops working.

The formula is:
{=IFERROR(INDEX(CM_ALL,SMALL(IF(($B2=CM_SubCat),ROW(CM_SubCat)-MIN(ROW(CM_SubCat))+1,""),ROWS($L$2:L2)),6),"")}

Any help on the error or a better way to do it would be appreciated. I’d like to avoid macros if possible.

Thanks in advance for any help.

Attempting to attached book.

Ah, I see. The culprit is ROWS($L$2:L2)

When Value in Column B changes from "Hiring Process" to "Driver Training Programs"...
Row counter should reset, since lookup value changes, but there isn't logic in the formula for that.
And since there is no match for SMALL(, 10). You see no result.

Amend your formula to use Column B and Countif logic.

Formula:

Please Login or Register  to view this content.

Confirm as Array (CTRL + SHIFT + ENTER).

Thank you, so much! Works exactly like I need it.

You are welcome.
If this solved your issue, please mark the thread as solved by using Thread tool found at top of your initial post.