How to return multiple headings

[CG2018]

Hi,
I have been able to use the formula below, to return a catogoery when product and type is selected i.e Item 1 B, returns Cat 2.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Cat 1[/TD]
[TD]Cat 2[/TD]
[TD]Cat 3[/TD]
[TD]Cat 4[/TD]
[TD]Cat 5[/TD]
[TD]Cat 6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item 1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item 2[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item 3[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Product[/TD]
[TD]Item 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Type[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Result[/TD]
[TD]Cat 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In cell B8 i have the formula :
=INDEX(B1:G1,,MATCH(B7,INDEX(A2:G4,MATCH(B6,A2:A4,0),0)))

I am now failing to modify this to return multiple cat's i.e Item 3, C to return Cat 1, Cat 3, Cat 4.

help please

Thank you

Colin

 

[hurgadion]

Hi,
try maybe array formula in B8 (Ctrl+Shift+Enter) and fill down:

=INDEX($A$1:$G$1,SMALL(IF(OFFSET($B$1:$G$1,RIGHT($B$6,1)*1,0,1,7)=$B$7,COLUMN($B$1:$G$1),""),ROW(A1)))

Best regards...

 

[CG2018]

Hi,
thank you for the quick response, i have tried that. It only returns one result, im looking for the Cell to return all the cats which have type C, so for item 3 it should be three results.

 

[hurgadion]

OK,
try to use above Formula in Cells B9, B10... If You want to have all Cats in one Cell, better will be VBA Code, I think...

 

[CG2018]

Perfect Thank you, i can hide that else where and use the concatenate

 

[hurgadion]

exactly... without VBA code is rather best Way...

 

[AhoyNC]

Here is another option:
This is an array function that must be entered with CTRL-SHIFT-ENTER.
Drag formula across columns as needed.

Excel Workbook
	A	B	C	D	E	F	G
1		Cat 1	Cat 2	Cat 3	Cat 4	Cat 5	Cat 6
2	Item 1	A	B	C	D	E	F
3	Item 2	B	C	D	E	F	A
4	Item 3	C	D	C	C	E	F
5
6	Product	Item 3
7	Type	C
8	Result	Cat 1	Cat 3	Cat 4
Sheet