return column header for each value occurrence in a table

[Rosa94]

Hello,

I have a table countries and jobs in that country. I am looking to select the job and get all the countries that job is in. Example: select Janitor and the output will show USA,China and Canada

USA	Spain	China	Canada	Mexico
Janitor	Payroll	Janitor	HR	Manager
	Operator	Payroll	Janitor
	Manager2	DVP
		HR

<tbody>
</tbody>

Is this something excel can do or is this something only access can process?
Thank you for your help.
Rosa

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E
1	USA	Spain	China	Canada	Mexico
2	Janitor	Payroll	Janitor	HR	Manager
3		Operator	Payroll	Janitor
4		Manager2	DVP
5			HR
6
7
8	Janitor	USA
9		China
10		Canada
Report

Cell Formulas
Range	Formula
B8	=IFERROR(INDEX($A$1:$E$1,AGGREGATE(15,6,(COLUMN($A$1:$E$1)-COLUMN($A$1)+1)/($A$2:$E$5=$A$8),ROWS($A$1:$A1))),"")

 

[Rosa94]

You're AMAZING!!!!
can you please explain each piece of the formula for me? My table is much larger and am not sure how the "(15,6," comes into play.
THANK YOU SOOOO MUCH!!!

 

[Fluff]

Have a look here & see if it helps. https://exceljet.net/formula/extract-all-partial-matches
All you need to change is the
$A$1:$E$1
to ensure it covers the range of your headers, and
$A$2:$E$5
to cover the range of data (excludig the header)

 

[Rosa94]

Thank you soooo much!!!

Do you have a suggestion of maybe something better to use if i'm doing this on a LARGE scale? Ex a table of 16,000 cells of data and 130 jobs to look for with possibly 100 countries.

 

[Fluff]

If the formula is too slow, then you could use either a database, or VBA.

 

[Fluff]

It may also be possible to do this with Power Query/Get & Transform

 

[sandy666]

It may also be possible to do this with Power Query/Get & Transform

Thanks Fluff

USA	Spain	China	Canada	Mexico		Name	Janitor
Janitor	Payroll	Janitor	HR	Manager
	Operator	Payroll	Janitor			Header
	Manager2	DVP				Canada
		HR				China
						USA

With PowerQuery and PivotTable

[SIZE=1]// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Demote = Table.DemoteHeaders(Source),
    Transpose = Table.Transpose(Demote),
    Unpivot = Table.UnpivotOtherColumns(Transpose, {"Column1"}, "Attribute", "Value"),
    ROC = Table.SelectColumns(Unpivot,{"Column1", "Value"}),
    Rename = Table.RenameColumns(ROC,{{"Column1", "Header"}, {"Value", "Name"}})
in
    Rename[/SIZE]

 

[Rosa94]

Oh man! I'm excited and scared at the same time.

I am not familiar with PowerQuery and have not used PivotTables very often. Is there a a link you can send me to help me figure out your beautiful work?

Have I told you how amazing your are?

 

[sandy666]

but solution need PowerQuery

you can download it here: Example file

Excel 2010 / 2013 require PowerQuery add-in (from MS site for free)
Higher version has PowerQuery (aka Get&Transform) built-in