Excel 365 filter formula: Return records in the column based its header's text value

[bradb2887]

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( "Responsible Party"), "--")

Thank you

 

[Habtest]

Hi, so you may locate the filter condition column by using OFFSET(MATCH()). try this:

=FILTER(A1:C5,OFFSET(A1:A5,,MATCH("Person",A1:C1,0)-1)="JJ")

 

[Fluff]

Another option

=FILTER(A1:F100, COLUMN(A1:F1)=MATCH("Responsible Party",A1:F1,0), "--")

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

https://www.reddit.com/r/excel/comments/n82epn

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jasonb75]

This question could be interpreted in many ways. The way the post reads it could be the matched column to return in the results or the matched column to use as the criteria, with or without additional row criteria in either case.

=FILTER(A1:F100,INDEX(A1:F100,0,MATCH("Person",A1:F1,0))="Name","--")

 

[bradb2887]

Thank you

 

[Fluff]

Glad we could hel & thanks for the feedback.