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 !

 

[abaker77]

OMG - what's an order of magnitude beyond 'BRILLIANT' ? Because that's what your solution is.
Thank you so much.

 

[bebo021999]

Thanks and nice to hear it works.
That request is not too complicate. Its typical using of AGGREGATE function, upgraded from Ex2010 and later.

 

[abaker77]

What is an order of magnitude beyond "Brilliant?" Because that's what your solution is.
THANK YOU.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find an item's group using a formula - no VBA, no Power Query
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.