Multiple matches in one row (no array formula)

So I am capable of doing multiple matches without an array, utilizing a helper column, but I am having trouble coming up with a way to do them in one column via a formula.

Basically, I am utilizing a countif to determine which match number the stuff is in the helper column, then utilizing either rows() or columns() to get that match number depending on which way I am dragging.

I am sort of stuck on how I'd list multiple criteria at once in a single column utilizing a single formula though (i'm thinking along the lines of offsetting it by largest match number, but I don't know how I'd do that for a varying amount of criteria).

Basically, instead of a table format, I'd want it to give all matches of the first criteria, followed by the next, etc. until all matches are found, then return errors if formula is still dragged down. The big challenge is I want to do this without an array formula if possible. I don't mind volatile functions as much, but would like to avoid them too if possible. Helper columns are totally okay too.

Example attached.

While I take a look at this, may I enquire what your aversion is to array formulae??

Not sure if this'll be much quicker....

=IFERROR(INDEX($B:$B,SMALL(INDEX(($A$2:$A$8<>$F2)*10^10+ROW($A$2:$A$8),0),COLUMNS($G2:G2))),"")

Copy across and down from G2.

Actually the practice of inserting one or more INDEX functions into a construction for the sole purpose of avoiding CSE (CTRL+SHIFT+ENTER) renders the resulting construction less efficient than the equivalent CSE version. It is an extra function call, after all.

More importantly, required keystroke combination aside, the non-CSE INDEX version is no less of an 'array formula' than the non-INDEX CSE one.

Regards

How many categories of animal are there in your real data?

In this case, a single-formula set-up simply cannot be achieved without resource to a formula which processes all items within the arrays passed.

Regards

Just because it does not required CSE, AGGREGATE is no less an 'array formula'. The same applies to SUMPRODUCT. And others.

Regards

Any array/non-array formulae that spring to mind would simply not be practical for that number of categories.

I know little or nothibg about VBA, but it's probably your only practical way forward.

I suppose it would not be acceptable to copy/paste you helper column to another sheet, sort it alphabetically (you may need to change the number format to show 3 digits - 001, 002, etc) and then do a simple lookup....

Try this out... there's only one AF-word (array formula) in it. It calculated 5,000 rows in less than 3 seconds... I took you very much at your word when you said that you don't mind helpers... If you can tolerate (another) AF-word formula, you could get it to list your unique categories in alphabetical order. I did it manually here (there were only 3; so it wasn't a huge strain).

The key formula is the Array formula which repeats the category names by the appropriate amount:

=IFERROR(INDEX($F$2:$F$4,MATCH(0,--(COUNTIF($I$1:I1,$F$2:$F$4)=$G$2:$G$4),0)),"")

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.