Generate a list of products based on what category it's assigned to

Try a pivot table

Enter formula in C2 and copy across and down

Formula:

Please Login or Register  to view this content.

v	A	B	C	D
1	PRODUCT	CATEGORY	Snacks	Drinks
2	Cookies	Snacks	Cookies	Water
3	Pretzels	Snacks	Pretzels	Soda
4	Carrots	Snacks	Carrots	Juice
5	Apples	Snacks	Apples	Milkshake
6	Water	Drinks
7	Soda	Drinks
8	Juice	Drinks
9	Milkshake	Drinks

One way...

Data Range

	A	B	C	D	E
1	PRODUCT	CATEGORY	------	CATEGORY	PRODUCT
2	Cookies	Snacks		Drinks	Water
3	Pretzels	Snacks			Soda
4	Carrots	Snacks			Juice
5	Apples	Snacks			Milkshake
6	Water	Drinks
7	Soda	Drinks
8	Juice	Drinks
9	Milkshake	Drinks

This formula entered in E2:

=IF(ROWS(E$2:E2)>COUNTIF(B:B,D$2),"",INDEX(A:A,MATCH(D$2,B:B,0)+ROWS(E$2:E2)-1))

Copy down until you get blanks.

Originally Posted by popipipo

Try a pivot table

Unfortunately, I need it to work with formulas. Thanks, though!

Originally Posted by kitpierce

if you could explain the elements of it (why the row and countif functions are used, for example and what they're doing in the formula).

=IF(ROWS(E$2:E2)>COUNTIF(B:B,D$2),"",INDEX(A:A,MATCH(D$2,B:B,0)+ROWS(E$2:E2)-1))

This portion is an error trap:

=IF(ROWS(E$2:E2)>COUNTIF(B:B,D$2),"",

It counts the number of cells the formula is copied to compared to the count of drinks. If/when the number of cells is greater than the count then the formula returns a blank which means all the relevant data has been extracted.