Find a column header on another sheet that a value matches on


I want to find a cell value that is in another sheet then return the column header (always minus 2 columns) that the cell value matches on.

In the example below I want a formula in S402 that searches the sheet lineups for the same value and then gets the header AC1.

So If I search lineups for Shantanu (#2L2V28CJG) in S402 I want the value to be #Ash Empire

  • The header I want is always 2 columns back from the column the match is in
  • There is only EVER one match or no match
  • The main sheet is called signups

I have tried query/match but just cant work it out. Thanks

This is the signups sheet
searchsheet

This is the lineups sheet

lineupsheet