Multiple output for every match

[TheN]

So I have a problem where I want to display all the matches for my data, not just the first.

I've been told that this monster of a formula:

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

works entered in array format (ctrl+shift+enter), but I foresee issues since there is a whole lot of data (16,000+ entries per column)

Is there a way to use a similar (less bulky) formula to yield the same results, and/or a way to transpose the output possibly?

For example, the data I'd be matching is still in columns, but when I drag the formula, I would want to drag it to the right instead of down for more results. That way, I would be able to match everything at once, instead of one at a time.

Also, how do I go about "dragging" an array formula? Totally new to them. If someone wouldn't mind explaining the logic behind their solution(s) also, that would be amazing. I'd love to learn how to utilize formulas such as these. I don't totally understand what the row/small functions are doing in the above formula. I believe the small function is telling it which match to use, but that's about all I get from it.

Desired:

Food	Vegetable		Lookup	Matches
Food	Fruit		Food
Hobby	Swimming		Hobby
Person	Edward		Person
Food	Meat
Person	Erica
Hobby	Singing
Hobby	Hiking

Currently:

Food	Vegetable		Lookup	Food
Food	Fruit		Matches
Hobby	Swimming
Person	Edward
Food	Meat
Person	Erica
Hobby	Singing
Hobby	Hiking

[Tony Valko]

What version of Excel does this have to work in?

The formula you posted is doing twice the work!

[TheN]

2010 and beyond would be fine. I don't think it has to work on anything prior to that.

You can ignore the if(iserror()) part then. Just use everything after ,"",

all that does is remove the errors from outputs, and replaces it with blank cells instead (I'm at least familiar with that part of the code).

[Tony Valko]

You can ignore the if(iserror()) part then. Just use everything after ,"",

all that does is remove the errors from outputs, and replaces it with blank cells instead

We can use a more efficient error trap. You don't want a bunch of #NUM! errors showing up do you?

[TheN]

I'm open to anything, as long as it works

As I said, what I am asking for is just slightly beyond my grasp of functions.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G	H
1	Food	Vegetable	------	Lookup	Matches			------
2	Food	Fruit		Food	Vegetable	Fruit	Meat
3	Hobby	Swimming		Hobby	Swimming	Singing	Hiking
4	Person	Edward		Person	Edward	Erica
5	Food	Meat
6	Person	Erica
7	Hobby	Singing
8	Hobby	Hiking

This array formula** entered in E2:

=IFERROR(INDEX($B:$B,SMALL(IF($A$1:$A$8=$D2,ROW(A$1:A$8)),COLUMNS($E2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to E4 then across until you get a column full of blanks.

[TheN]

Seems to work, thanks

Care to explain it to me, lots of functions I've never seen before.

One last question. Will this work on multiple sheets? For example everything I am looking up is on one sheet, but all of the outputs are on a different sheet. Literally never used array formulas, so not aware of their limitations. I've just been told they can crash larger sheets because they create more strain than regular formulas.

Very grateful for your help thus far.

[Tony Valko]

If the data is on a different sheet just include that sheet name like so:

=IFERROR(INDEX(Sheet2!$B:$B,SMALL(IF(Sheet2!$A$1:$A$8=$D2,ROW(Sheet2!A$1:A$8)),COLUMNS($E2:E2))),"")

How it works...

Here's an explanation I wrote for a very similar application (I'm a very slower typer and don't want to "reinvent the wheel"!)

https://www.excelforum.com/showthread.php?t=909632

Array formulas get a bad reputation mostly due to misinformation propagated over the internet.

Yes, some array formulas are calculation intensive. However, the reason for that is the task at hand is very complex and it may not be possible to do it any other way.

It also depends greatly on how much data the array formula is processing.

[TheN]

Thanks again!

I'll definitely have to look more into the advantages and disadvantages of array formulas.

[Tony Valko]

You're welcome. Thanks for the feedback!