Forum Discussion
Advanced Excel Filter
In the table below, I want to filter rows where the Job Applicant (Name column), has a driver’s license and the expected salary range is NAD1000 – NAD4999 in the Answer Column. Once the filter is ap...
=LET(name,A2:A23,
licence,COUNTIFS($A$2:$A$23,name,$B$2:$B$23,"Do you have a valid driver's licence",$C$2:$C$23,"Yes")=1,
salary,COUNTIFS($A$2:$A$23,name,$B$2:$B$23,"In what range does your expected monthly salary fall?",$C$2:$C$23,"NAD1000 - NAD4999")=1,
result,UNIQUE(FILTER(name,licence+salary=2)),
result)If you work with Office365 or 2021 or Excel online you can try above formula which seems to work in my sheet.
Thanks for your suggestion. However I was hoping for a solution that can filter the records in the Excel table, making use of criteria from cell values so that the user don't have to change formulas, but simply change the criteria in the cells to apply different filters. Almost like using the Advanced Filter option in Excel.
In the attached file you can enter the filter criteria in cells G1:J1 and then click the button in cell F3 to execute the macro for the advanced filter.
- OliverScheurich
Thanks a lot, will check it out.
