reverse Index/Match?

[orsm6]

Hi - I can't solve his and need some help - open to any formulas, not just index/match etc.

Range: A2:G2 holds departments names.
in columns A : G from row 3 there are lists of topics for each department.

In column O i am trying to return the list of topics for the department based on the value of O1.

so as example: if cell O1 says HSSE it would return all the values in column A3 and down
if O1 said Planning, it would return all the values in column F3 and down

TIA, hope that made sense.

 

[Toadstool]

Hi Orsm6,

Does this do what you want?

Orsm6.xlsx
	A	B	C	D	E	F	G	H							O
1															Planning
2	HSSE	Finance	Marketing	Sales	DevOps	Planning	Janitors								IP Skills
3	IP Skills	Maths	IP Skills	IP Skills	Maths	IP Skills	Falconry								HSSE
4	HSSE	Falconry	Maths	Maths	Painting	HSSE									Cooking
5	ADD		Painting	Painting		Cooking
6	Literature		Literature	Literature
7
8
Sheet1

Cell Formulas
Range	Formula
O2:O8	O2	=INDEX($A$3:$G$999,AGGREGATE(15,6,ROW($A$3:$A$999)-ROW($A$2),ROW()-ROW($O$1)),MATCH($O$1,$A$2:$G$2,0))&""

Cells with Data Validation
Cell	Allow	Criteria
O1	List	=$A$2:$G$2

 

[orsm6]

Hi Orsm6,

Does this do what you want?

Orsm6.xlsx
	A	B	C	D	E	F	G	H							O
1															Planning
2	HSSE	Finance	Marketing	Sales	DevOps	Planning	Janitors								IP Skills
3	IP Skills	Maths	IP Skills	IP Skills	Maths	IP Skills	Falconry								HSSE
4	HSSE	Falconry	Maths	Maths	Painting	HSSE									Cooking
5	ADD		Painting	Painting		Cooking
6	Literature		Literature	Literature
7
8
Sheet1

Cell Formulas
Range	Formula
O2:O8	O2	=INDEX($A$3:$G$999,AGGREGATE(15,6,ROW($A$3:$A$999)-ROW($A$2),ROW()-ROW($O$1)),MATCH($O$1,$A$2:$G$2,0))&""

Cells with Data Validation
Cell	Allow	Criteria
O1	List	=$A$2:$G$2

it most certainly does - thank you very much.

 

[Peter_SSs]

it most certainly does

I think the much simpler version in O2 below should do the same as the suggested formula.
Or even simpler in P2 if you use a helper cell like P1

Also, since you have Excel 365 you may be able to employ one of the alternatives given in Q2:S2. One advantage of these is that they should not need to be copied down for the length of your data in columns A:G but should automatically 'spill' down the rows. S2 again uses the helper cell.

20 12 21.xlsm
	A	B	C	D	E	F	G	H							O	P	Q	R	S
1															Planning	6
2	HSSE	Finance	Marketing	Sales	DevOps	Planning	Janitors								IP Skills	IP Skills	IP Skills	IP Skills	IP Skills
3	IP Skills	Maths	IP Skills	IP Skills	Maths	IP Skills	Falconry								HSSE	HSSE	HSSE	HSSE	HSSE
4	HSSE	Falconry	Maths	Maths	Painting	HSSE									Cooking	Cooking	Cooking	Cooking	Cooking
5	ADD		Painting	Painting		Cooking
6	Literature		Literature	Literature
7
Extract Column

Cell Formulas
Range	Formula
P1	P1	=MATCH(O1,A2:G2,0)
O2:O7	O2	=INDEX(A3:G3,MATCH(O$1,A$2:G$2,0))&""
P2:P7	P2	=INDEX(A3:G3,P$1)&""
Q2:Q8	Q2	=FILTER(A3:G9&"",A2:G2=O1,"")
R2:R8	R2	=INDEX(A3:G9,0,MATCH(O1,A2:G2,0))&""
S2:S8	S2	=INDEX(A3:G9,0,P1)&""
Dynamic array formulas.

 

[orsm6]

I think the much simpler version in O2 below should do the same as the suggested formula.
Or even simpler in P2 if you use a helper cell like P1

Also, since you have Excel 365 you may be able to employ one of the alternatives given in Q2:S2. One advantage of these is that they should not need to be copied down for the length of your data in columns A:G but should automatically 'spill' down the rows. S2 again uses the helper cell.

20 12 21.xlsm
	A	B	C	D	E	F	G	H							O	P	Q	R	S
1															Planning	6
2	HSSE	Finance	Marketing	Sales	DevOps	Planning	Janitors								IP Skills	IP Skills	IP Skills	IP Skills	IP Skills
3	IP Skills	Maths	IP Skills	IP Skills	Maths	IP Skills	Falconry								HSSE	HSSE	HSSE	HSSE	HSSE
4	HSSE	Falconry	Maths	Maths	Painting	HSSE									Cooking	Cooking	Cooking	Cooking	Cooking
5	ADD		Painting	Painting		Cooking
6	Literature		Literature	Literature
7
Extract Column

Cell Formulas
Range	Formula
P1	P1	=MATCH(O1,A2:G2,0)
O2:O7	O2	=INDEX(A3:G3,MATCH(O$1,A$2:G$2,0))&""
P2:P7	P2	=INDEX(A3:G3,P$1)&""
Q2:Q8	Q2	=FILTER(A3:G9&"",A2:G2=O1,"")
R2:R8	R2	=INDEX(A3:G9,0,MATCH(O1,A2:G2,0))&""
S2:S8	S2	=INDEX(A3:G9,0,P1)&""
Dynamic array formulas.

Thank you to you both - these were great solutions. Toadstool please don't be offended - I have marked Peter's reply as solution not because yours didn't work, just that if others come along they can get the benefits of all the solutions offered.

thanks again.

 

[Peter_SSs]

Thank you to you both

You're welcome. Glad we could help.

As a matter of interest, which formula suits you the best?

 

[orsm6]

You're welcome. Glad we could help.

As a matter of interest, which formula suits you the best?

O2

 

[Peter_SSs]

O2

Terrific, thanks.

(I was asking because if it was one of the dynamic array formulas in Q:S, it would be possible, with a more complicated formula, to stop them spilling right down to the end of the data, even though the bottom cells will likely be blank. With O2, you will have to copy it down as far as the longest list is likely to go. )