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 applied, only Ester and Ciara records should remain.
| Name | Question | Answer |
| Nakulonda | Do you have a valid driver's licence | No |
| Ester | Do you have a valid driver's licence | Yes |
| MENESIA | Do you have a valid driver's licence | No |
| Ciara | Do you have a valid driver's licence | Yes |
| Llewellyn Namuandi | Do you have a valid driver's licence | Yes |
| Denzel Nel | Do you have a valid driver's licence | Yes |
| Nekulilo Alugongo | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| MAANO PATEMOSHELA SHEVANYENGA | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| Lukas Iilonga | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| Ndjiisanee Ngupandjara | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| Gerson Thomas | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| Ester | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| MENESIA NEKWAYA | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| Ciara | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| Maria Egumbo | In what range does your expected monthly salary fall? | NAD1000 - NAD4999 |
| Shane | In what range does your expected monthly salary fall? | NAD5000 - NAD9999 |
| Perpetua Nakulonda | In what range does your expected monthly salary fall? | NAD5000 - NAD9999 |
| Haufiku | In what range does your expected monthly salary fall? | NAD5000 - NAD9999 |
| Muzuva Mungunda | In what range does your expected monthly salary fall? | NAD10000 - NAD14999 |
| Denzy | In what range does your expected monthly salary fall? | NAD10000 - NAD14999 |
| Namuandi | In what range does your expected monthly salary fall? | NAD15000 - NAD19999 |
| Olga | In what range does your expected monthly salary fall? | NAD30000 - NAD34999 |
Thanking you in anticipation for you assistance.
Elmo
4 Replies
=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.
