Reference a cell value as the Sheet name in a Match formula-CodePudding

I want to match the value in cell A2 of my sheet to a header value on a different sheet and post back the matched column

I have a dropdown of sheet names in cell F5 on a sheet called Filter_Maker

I need to reference this cell value as the sheet name in a match formula to the cell value in A2

I am trying

=MATCH($A2,indirect("'"Filter_Maker!F5"'!$A1:$H1"),0)

I am getting Formula parse error

Thanks for any assistance on this

CodePudding user response：

I got it. For anyone looking for this

where

indirect(Filter_Maker!F5&"!$A2:$Z")

references the data range. And

SUBSTITUTE(ADDRESS(1,MATCH($A2,indirect(Filter_Maker!F5&"!$A1:$H1"),0),4),1,"")

Selects the column to postback

=query(indirect(Filter_Maker!F5&"!$A2:$Z"),
  "Select "&SUBSTITUTE(ADDRESS(1,MATCH($A2,indirect(Filter_Maker!F5&"!$A1:$H1"),0),4),1,"")
  ,1)

CodePudding user response：

try:

=FILTER(INDIRECT(Filter_Maker!F5&"A2:Z"); MATCH(A2; 
        INDIRECT(Filter_Maker!F5&"A2:Z1"); ))