Dynamic List Based on 'X' Marks in Table

[jimmycooker]

Hi,

Can someone help me with a formula to get this subset of letters:

1 2 3 4 5 6 7
A x x x x x
B x x x x
C x x x x
D x x x x x
E x x x x
F x x x x

Create Dynamic List of letters 'A to F' based on cell reference '5'

Result required (based on 'X' marks):

A
C
D
E

 

[jimmycooker]

Ignore the last email, the table didn't come out properly.

Can someone help me with a formula to get this subset of letters:

1 2 3 4 5 6 7
A x x x x x
B x x x x
C x x x x
D x x x x x
E x x x x
F x x x x

Create Dynamic List of letters 'A to F' based on cell reference '5'

Result required (based on 'X' marks):

A
C
D

 

[jimmycooker]

Hi,

Can someone help me with a formula to get this subset of letters:

--1 2 3 4 5 6 7
A x x x x x
B x x x x
C x x x x
D x x x x x
E x x x x
F x x x x

Create Dynamic List of letters 'A to F' based on cell reference '5'

Result required (based on 'X' marks):

A
D

or

Create Dynamic List of letters 'A to F' based on cell reference '1'

Result required (based on 'X' marks):

A
B
C
D
E
F

 

[Fluff]

Hi & welcome to MrExcel
How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	7		5
2	A	X		X		X		X		A
3	B		X	X	X		X			C
4	C	X		X		X	X			D
5	D			X		X		X
6	E	X		X			X	X
7	F		X		X		X
Appendix

Cell Formulas
Range	Formula
J2	=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/(($B$1:$H$1=$J$1)*($B$2:$H$7="X")),ROWS($1:1))),"")

 

[jimmycooker]

Wow.....thank you!

 

[Fluff]

You're welcome

 

[jimmycooker]

Could you adapt it so instead of cells containing on 'X' to cells containing the letter X + other info e.g. X (2)

 

[Fluff]

How about
=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/(($B$1:$H$1=$J$1)*($B$2:$H$7<>"")),ROWS($1:1))),"")

This will return a value if the cell is not blank

 

[jimmycooker]

How about
=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/(($B$1:$H$1=$J$1)*($B$2:$H$7<>"")),ROWS($1:1))),"")

This will return a value if the cell is not blank

Hi, thanks for the update above........it didn't work unfortunately because the table i'm checking is full of formulas (i think).....

Would it be possible to add 'and' or 'or' checking to this formula instead?

=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/(($B$1:$H$1=$J$1)*($B$2:$H$7<>"")and / or if ($B$2:$H$7="X(2)")),ROWS($1:1))),"")

Thanks!

 

[Fluff]

How about
=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/(($B$1:$H$1=$J$1)*(LEFT($B$2:$H$7,1)="X")),ROWS($1:1))),"")
This will return anything that starts with X