Excel 365 filter formula: Return records in the column based its header's text value (eg, using match)

Question

I know this is the criteria for the Filter function in Excel 365: =FILTER(array,include,[if_empty])

I want to return results based the text in a column header. I won't know the column name (for example, if I want to search for the column with the "Person" header, the column number could change (the "Person" column could be C2, or E2, or something else)

Can the "Include" criteria use Match? Something like. I'm having difficulty getting it to work:

=FILTER(A1:F100, COLUMN( MATCH( "Person"), "--")

Thank you

Answer 1

Hi @Brad Burton

FILTER the header row then FILTER the table/range:

in F4:
=FILTER(Table1,
FILTER(Table1,Table1[#Headers] = G2) = F2,
"no match"
)

in F8:
=FILTER(A3:D6;
FILTER(A3:D6;A2:D2 = G2) = F2;
"no match"
)