Google Sheets - Dynamic function to find name of a Column (based on values in said column)-CodePudding

It seems like a fairly easy function, but I simply cannot wrap my head around it.

https://i.stack.imgur.com/GDWdu.png - Picture to help you imagine.

I have 2 columns filled with data (B3:C), and I have another column (E), where I input data (data validation from the B3:C dataset). I need a function (in column F) that will match the name of one of the columns (B2 or C2), based on the value in E.

Example: If E4 is in the column B, then F4 is B2.

Thank you very much!

CodePudding user response：

There are many ways to do this, including filter(match()) and vlookup(). Try this fill-down formula:

=filter($B$2:$C$2, { match(E2, $B$3:$B, 0), match(E2, $C$3:$C, 0) } )

...or this array formula that fills all of the column automatically:

=arrayformula( 
  iferror( 
    vlookup( 
      E2:E, 
      { 
        B3:B, iferror(B3:B/0, B2); 
        C3:C, iferror(C3:C/0, C2) 
      }, 
      2, 
      false 
    ) 
  ) 
)