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

Brad Burton 21 Reputation points
2021-05-08T21:12:50.477+00:00

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

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,618 questions
{count} votes

Accepted answer
  1. Lz._ 8,981 Reputation points
    2021-05-09T06:46:50.417+00:00

    Hi @Brad Burton

    FILTER the header row then FILTER the table/range:

    PIC

    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"
    )


0 additional answers

Sort by: Most helpful