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

[TABLE="width: 158"]
<colgroup><col width="79" span="2" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 79"]Type[/TD]
[TD="class: xl65, width: 79"]Item[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Vegetable[/TD]
[TD]Cucumber[/TD]
[/TR]
[TR]
[TD]Vegetable[/TD]
[TD]Potato[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]Vegetable[/TD]
[TD]Zucchini[/TD]
[/TR]
</tbody>[/TABLE]


Now, I want these lists to automatically populate:
[TABLE="width: 158"]
<colgroup><col width="79" span="2" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 79"]Fruit[/TD]
[TD="class: xl65, width: 79"]Vegetable[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Cucumber[/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD]Potato[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Zucchini[/TD]
[/TR]
</tbody>[/TABLE]


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?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Type[/td][td]Item[/td][td][/td][td]Fruit[/td][td]Vegetable[/td][/tr]

[tr][td]

2​

[/td][td]Fruit[/td][td]Apple[/td][td][/td][td]Apple[/td][td]Cucumber[/td][/tr]

[tr][td]

3​

[/td][td]Vegetable[/td][td]Cucumber[/td][td][/td][td]Peach[/td][td]Potato[/td][/tr]

[tr][td]

4​

[/td][td]Vegetable[/td][td]Potato[/td][td][/td][td][/td][td]Zucchini[/td][/tr]

[tr][td]

5​

[/td][td]Fruit[/td][td]Peach[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Vegetable[/td][td]Zucchini[/td][td][/td][td][/td][td][/td][/tr]
[/table]
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!