Looking for a formula that can identify which column value is being used per row value

Good evening folks,

Been a little while since I was on this forum but I was wondering if someone could help me out or point me in the right direction of solving this specific question I have.

Basically I am looking for a formula that can go through this table I have created and identify which column header(Area) is being used as per the value inputted(x) below it in each row. I have attached a sample of the problem I am having and put a yellow column to the right hand side of how the formulas answer should be.

Any help on this would be hugely appreciated guys and thanks for taking some time on having a look

Hi, to all!

Another option could be this UDF:

PHP Code: 

Function JoinCond$(RngCond As Range, Cond$, RngRes As Range, Delim$)
    Dim a&, d&, c As Range, aux
    
    a = Application.CountIf(RngCond, Cond)
    ReDim aux(1 To a)
    
    For Each c In RngCond
        If LCase(c) = LCase(Cond) Then
            d = 1 + d
            aux(d) = RngRes(c.Row - RngCond.Row + 1, _
                        c.Column - RngCond.Column + 1)
        End If
    Next c
    JoinCond = Join(aux, Delim)
End Function 


And you can use like a Excel formula like this:
[O4] : =JoinCond(C4:N4,"x",C$3:N$3," & ")

Check file. Blessings!

Really Appreciate the responses guys thanks so much . Im not very good when it comes to VBA but is there a way to have it so that it can pick up when the values of each rows are changed i.e added or removed ? When I try to mess around with inputting different (x) in different row boxes I am just faced with a !NAME error. Or parhaps I am just struggling to grasp certain aspects.

Thanks none the less guys , you are all saints !

Hi, Rybags!

When you open the file, you must click on "Enable macros" in the yellow bar that appears at the top of the Excel workspace, so that the formula works for you. The NAME errors in Excel are because the functions used are not being recognized. Blessings!