If Column is X, display text

[FelVuk]

I have a list of names and all the countries. Column 1 is filled with names and the following columns represent all the countries of the world. In each row, there's an 'X' under the country that person has visited.
On another tab, I'm listing some of these names using the formula FILTER. In the following column I'd like to list every country where there's an 'X' for that person.
For example, in Cell A3, I have this formula: =FILTER(Table1[Name, first name];Table1[Age]=“20-25”;””), which gives Thomas. In Cell B3, I want all the countries where Thomas has an 'X', so that in Cell U3, I will get: Belgium, Tanzania, Poland.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[FelVuk]

My work pc wont let me use XL2BB.

Sheet 1:

Name	Age 20-25	Belgium	Poland	Tanzania
Thomas	Yes	X	X
Kevin	Yes		X
Peter	No			X

Sheet 2:

Name (=FILTER(Table1[Name];Table1[Age 20-25]="Yes";""))	Countries ?
Thomas
Kevin

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Age 20-25	Belgium	Poland	Tanzania
2	Thomas	Yes	X	X				Thomas	Belgium	Poland
3	Kevin	Yes		X				Kevin	Poland
4	Peter	No			X
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=FILTER(Table1[Name],Table1[Age 20-25]="Yes")
I2:J2,I3	I2	=TOROW(IF(FILTER(Table1[[Belgium]:[Tanzania]],Table1[Name]=H2)="X",Table1[[#Headers],[Belgium]:[Tanzania]],1/0),2)
Dynamic array formulas.

 

[felixstraube]

How about?:
(you'll have to extend to the full range of your data)

Libro1
	A	B	C	D	E	F	G
1	Name	Belgium	Germany	Italy	Poland	Tanzania	USA
2	Thomas	X			X	X	X
3	Anna		X	X
4	Bill				X	X
5
6
7		Name	Countrys visited
8		Thomas	Belgium	Poland	Tanzania	USA
9		Anna	Germany	Italy
10		Bill	Poland	Tanzania
Hoja1

Cell Formulas
Range	Formula
C8:F8,C9:D10	C8	=FILTER($B$1:$G$1,FILTER($B$2:$G$4,$A$2:$A$4=B8)="X")
Dynamic array formulas.

 

[FelVuk]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Age 20-25	Belgium	Poland	Tanzania
2	Thomas	Yes	X	X				Thomas	Belgium	Poland
3	Kevin	Yes		X				Kevin	Poland
4	Peter	No			X
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=FILTER(Table1[Name],Table1[Age 20-25]="Yes")
I2:J2,I3	I2	=TOROW(IF(FILTER(Table1[[Belgium]:[Tanzania]],Table1[Name]=H2)="X",Table1[[#Headers],[Belgium]:[Tanzania]],1/0),2)
Dynamic array formulas.

The empty cells will show number 5 and the one marked with an x show number 45...
Sheet 2:

Name	Belgium	Germany	Italy
Thomas	45	5	5
Kevin	5	5	5

 

[FelVuk]

How about?:
(you'll have to extend to the full range of your data)

Libro1
	A	B	C	D	E	F	G
1	Name	Belgium	Germany	Italy	Poland	Tanzania	USA
2	Thomas	X			X	X	X
3	Anna		X	X
4	Bill				X	X
5
6
7		Name	Countrys visited
8		Thomas	Belgium	Poland	Tanzania	USA
9		Anna	Germany	Italy
10		Bill	Poland	Tanzania
Hoja1

Cell Formulas
Range	Formula
C8:F8,C9:D10	C8	=FILTER($B$1:$G$1,FILTER($B$2:$G$4,$A$2:$A$4=B8)="X")
Dynamic array formulas.

#N/A as response

 

[Fluff]

The empty cells will show number 5 and the one marked with an x show number 45...

I don't understand what you are asking, originally you wanted to return the name of the countries.

 

[FelVuk]

I don't understand what you are asking, originally you wanted to return the name of the countries.

When I enter your formula I don't return the countries but the cells are filled with number like in my previous response

 

[Fluff]

Are you saying that you don't get the names of the countries with the formula I suggested?