Match multiple values, then display as a list

[Kandazzle]

Hello,

I'm wondering if anyone is able to help, as this has started to take up a lot of my time, and I'm on the verge of giving up and reverting to doing it manually.

I have a list "Services" which can have multiple "internal services" associated with them. I have the master list, and I would like to return all "internal services" against the list of my "Services". I've successfully managed to do this horizontally, but I would like to do this vertically in a list (not just transpose it).

Is there a way.

I've done an example manually below to maybe help explain what I mean:

1. The master list of all services (dishes in this case)
2. My list of which services I care about
3. The formula I used to return all internal services (ingredients in this instance) (formula was: =IF(COLUMN()-6<=COUNTIF($A$2:$A$1442,$F2),INDEX($B$2:$B$1442,MATCH($F2,$A$2:$A$1442,0)+COLUMN()-7),"") )
4. How I would like the data displayed in the end.

 

[Kandazzle]

https://imgur.com/a/nlh92fL

for the image.. sorry, works computer, so I cant directly link

 

[cunningAce]

Hi,

Something like this should work,

Book1
	A	B	C	D	E	F
2	Dish	Ingredients			Dish
3	Cake	Flour			Cake
4	Cake	Sugar			Flour
5	Cake	Butter			Sugar
6	Bread	Flour			Butter
7	Bread	Yeast
8	Soup	Tomato
9	Soup	Basil			Sausage Roll
10	Soup	Salt			Flour
11	Sausage Roll	Flour			Sausage Meat
12	Sausage Roll	Sausage Meat			Salt
13	Sausage Roll	Salt			Pepper
14	Sausage Roll	Pepper
Multiple Matches

Cell Formulas
Range	Formula
E4	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$3,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$3:E3))),"")}
E5	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$3,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$3:E4))),"")}
E6	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$3,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$3:E5))),"")}
E7	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$3,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$3:E6))),"")}
E10	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$9,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$9:E9))),"")}
E11	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$9,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$9:E10))),"")}
E12	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$9,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$9:E11))),"")}
E13	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$9,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$9:E12))),"")}
E14	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$9,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($E$9:E13))),"")}
F8	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$9,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($F$3:F7))),"")}
F9	{=IFERROR(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$E$9,ROW($A$3:$A$14)-ROW($A$3)+1),ROWS($F$3:F8))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Kandazzle]

Firstly, thank you for the reply!!

So I may not have explained it as well as I originally thought I had. I have three columns

Master list with everything

Column 1 = Dish​

Column 2 = Ingredients
​

and

Column 3 = List with Dishes I care about

​

The format in column is what I would like, but I don't understand where it needs to source the lookup values from, to run against Column 1 & 2.

When I try it as is, I don't get any results.

Apologies if I've misunderstood something here

thanks

 

[cunningAce]

I'm not sure I follow what you are trying to achieve,
Maybe post an example of what you would like the result to look like.

The example I gave you needs to be entered using Ctrl-Shift-Enter as its an array formula and work wont in the usual way