Return all values

[_karl_]

Hello everyone-

I'm not quite sure how to phrase my question, so I'll ask by way of an example.

Let's say I have a dynamic data set like the one below. (Assume people are always adding, deleting, and changing entries.)

Type	Item
Fruit	Apple
Vegetable	Cucumber
Vegetable	Potato
Fruit	Peach
Vegetable	Zucchini

<colgroup><col width="79" span="2" style="width:60pt"> </colgroup><tbody>
</tbody>

Now, I want these lists to automatically populate:

Fruit	Vegetable
Apple	Cucumber
Peach	Potato
	Zucchini

<colgroup><col width="79" span="2" style="width:60pt"> </colgroup><tbody>
</tbody>

What formula would I enter under "Fruit" and "Vegetable" to accomplish this? Keep in mind that the number of fruits and vegetables is continuously changing. I have a feeling that it's going to take some sort of vlookup array formula, but I can't wrap my brain around it. I know that I could create a pivot table & add some VB to automatically refresh it when the data changes, but I'd like to handle this with a formula.

Thanks in advance for your help!

 

[FDibbins]

maybe something like this?

	A​	B​	C​	D​	E​
1​	Type	Item		Fruit	Vegetable
2​	Fruit	Apple		Apple	Cucumber
3​	Vegetable	Cucumber		Peach	Potato
4​	Vegetable	Potato			Zucchini
5​	Fruit	Peach
6​	Vegetable	Zucchini

D2=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$6=D$1,ROW($A$2:$A$6)),ROWS($A$1:A1))),"")
ARRAY entered using CTRL SHIFT ENTER, not just enter
Then copy down - then copy all of that across

 

[_karl_]

In that exact situation, it works; however, when I tried to apply it to my actual situation, I can't get it to work. Would anyone mind taking a look at the actual file? (Link below.) I'm trying to populate the yellow highlighted cells. I already attempted to modify the formula above & left that in there so you can spot any possible error. Thank you!

https://drive.google.com/drive/folders/0B4aiHbtOklZYN1lHWFEtcC1Va3M?usp=sharing

 

[Ingolf]

You use this formula:

=IFERROR(INDEX(MaterialsMaster!$B2:$B250,SMALL(IF(MaterialsMaster!$A$2:$A$250=A$1,ROW(MaterialsMaster!$A$2:$A$250)),ROWS(MaterialsMaster!$A$2:$A$250))),"")

and Ford gave you this formula:

=IFERROR(INDEX(MaterialsMaster!$B2:$B250,SMALL(IF(MaterialsMaster!$A$2:$A$250=A$1,ROW(MaterialsMaster!$A$2:$A$250)),ROWS(A$1:A1))),"")


ARRAY entered using CTRL SHIFT ENTER, not just enter
Then copy down - then copy all of that across

 

[FDibbins]

Cannot view your file, but what exactly did not work?

Based on Ingold's comment (thanks for that), you did not get the last argument right?

Also, change this...
=IFERROR(INDEX(MaterialsMaster!$B2:$B250,SMALL(IF(MaterialsMaster!$A$2:$A$250=A$1,ROW(MaterialsMaster!$A$2:$A$250)),ROWS(A$1:A1))),"")
to this...
=IFERROR(INDEX(MaterialsMaster!$B:$B,SMALL(IF(MaterialsMaster!$A$2:$A$250=A$1,ROW(MaterialsMaster!$A$2:$A$250)),ROWS(A$1:A1))),"")

 

[_karl_]

Those two changes did the trick. Thank you!