question

BradBurton-4016 avatar image
0 Votes"
BradBurton-4016 asked emizhang-msft commented

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

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

office-excel-itpro
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BradBurton-4016,
Just checking in to see if the information of Lz-3068 was helpful. Please let us know if you would like further assistance. If the response is helpful, please click "Accept Answer" and upvote it. Other partners who read the forums with the same issue can get more information from the correct result.
Thank you.

0 Votes 0 ·

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 edited

Hi @BradBurton-4016

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

[1]: https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261


demo.png (31.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.