Extracting all the values using Lookup (including duplicates)

[Zakky]

Hi, I am trying to extract all the countries that each product is sold in. It should work for all values (including duplicates) and not just the first value. I am not sure why it's returning product name and not the market name. I have presented the problem below and the solution (hand written). The spill should stop at the last value so I don't get lots of zeros. Any help would be greatly appreciated, many thanks. Image uploaded.

 

[Cubist]

Shouldn't America return for Product C?

Book1
	A	B	C	D	E	F	G	H	I
1
2	Product	Quality	Market		Product A	America	Australia	UK	Egypt
3	Product A	Good	America		Product B	Greece	Australia
4	Product B	Good	Greece		Product C	UK	America
5	Product A	Average	Australia
6	Product A	Excellent	UK
7	Product C	Poor	UK
8	Product B	Excellent	Australia
9	Product A	Excellent	Egypt
10	Product C	Average	America
11
Sheet15

Cell Formulas
Range	Formula
F2:I2,F3:G4	F2	=TOROW(FILTER($C$3:$C$10,$A$3:$A$10=E2,""),3)
Dynamic array formulas.

 

[Zakky]

Cubist, thankyou for the prompt reply. Amazing! Your solution works like a charm. Many thanks. Have a great day.