Find the group an item belongs to

[abaker77]

I am looking for a non-VBA solution, i.e. an Excel formula, that can find the group to which an item belongs. Put another way, I need to find the contents of the 1st row in a given column.
An image of what this looks like is attached - there is a table of codes (items, if you will) and the column headers are the Groups.
The idea is to enter a code into cell C2, then find (or lookup) the corresponding group (1st cell) to which that code belongs. The formula for this would be in cell C3. So in my example, code594 is in GroupD, so the formula in C3 should return the result GroupD.
It is possible that the code entered in C2 may be duplicated in the table, but in this case, I would just expect the 1st column that has that code to be returned.
If there was a way to find the column # of the code entered in C2, then I guess you could find the contents of the 1st cell in the applicable column, i.e. Group, but I can't figure it out.
Thank you so much !

 

[Fluff]

Which version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[sandy666]

with Power Query

GroupA	GroupB	GroupC	GroupD	GroupE	GroupF		Code		Group
code621	code436	code543	code359	code902	code619		code334		GroupA
code334	code709	code703	code459	code334	code614				GroupE
code169	code100	code591	code160	code905	code291
code476	code557	code223	code749	code717	code851
code964	code127	code768	code634	code409	code158
code931	code487	code623	code594	code477	code586
code160	code259	code107	code986	code261	code829

after changing Code refresh green table to see new Group(s)

let
    Code = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Matrix = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Matrix, {}, "Group", "Value"),
    Join = Table.NestedJoin(Code,{"Code"},Unpivot,{"Value"},"Table",JoinKind.FullOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Group"}, {"Group"}),
    Filter = Table.SelectRows(Expand, each [Code] <> null),
    TSC = Table.SelectColumns(Filter,{"Group"})
in
    TSC

 

[abaker77]

with Power Query

Code = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Matrix = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Matrix, {}, "Group", "Value"),
Join = Table.NestedJoin(Code,{"Code"},Unpivot,{"Value"},"Table",JoinKind.FullOuter),
Expand = Table.ExpandTableColumn(Join, "Table", {"Group"}, {"Group"}),
Filter = Table.SelectRows(Expand, each

 <> null),
    TSC = Table.SelectColumns(Filter,{"Group"})
in
    TSC

thank you for your Power Query solution but I really need to find a non-VBA solution. Ideally, I need a formula that can return the Group name.
Thanks for your time, anyway - I really appreciate it.
(I'm using Office 365, by the way.)

 

[sandy666]

No problem
Power Query is NOT a vba
update profile about excel version not a post

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I
1
2			Code211
3			Group 5
4
5		Group 1	Group 2	Group 3	Group 4	Group 5	Group 6	Group 7	Group 8
6		Code382	Code903	Code344	Code146	Code101	Code576	Code859	Code454
7		Code701	Code989	Code814	Code590	Code746	Code438	Code423	Code107
8		Code522	Code223	Code317	Code861	Code211	Code648	Code471	Code339
9		Code837	Code628	Code448	Code618	Code234	Code331	Code986	Code989
10		Code828	Code441	Code128	Code341	Code196	Code197	Code401	Code560
11
Main

Cell Formulas
Range	Formula
C3	C3	=INDEX(B5:I5,AGGREGATE(15,6,(COLUMN(B5:I5)-COLUMN(B5)+1)/(B6:I10=C2),1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:I10	Expression	=B6=$C$2	text	NO
B6:I10	Expression	=COUNTIFS($B$6:$I$10,B6)>1	text	NO

Please don't forget to update your account details & then scroll down & click save.

 

[abaker77]

Your formula solution is brilliant - it works perfectly. Thank you !

 

[Fluff]

You're welcome & thanks for the feedback.

 

[abaker77]

YES, extremely brilliant - a great solution.
One question - what is the recommendation if (in the example shown) there are duplicate codes?
This formula appears to return the 'last' group in which the duplicate item appears.

 

[bebo021999]

It such a year from last help. Not sure the helper is online or not, but try a little twist:

Put this in C3 then drag accross to D3,E3 as far as possible:

=IFERROR(INDEX($B$5:$I$5,AGGREGATE(15,6,(COLUMN($B$5:$I$5)-COLUMN($B$5)+1)/($B$6:$I$10=$C$2),COLUMNS($A:A))),"")