Advanced Filter with Formula

The request was to show all name of the doctor that match from 3 category of the filter.

Category 1 : the name of insurance card that will be used.

Category 2 : the age of the patient.

Category 3 : the type specialty doctor required.

Formula will be used is "FIND", "ISNUMBER" and "FILTER".

1. The idea was to get a "number" value if all category is match in doctor list, so will use "find".

2. As the match result should comes in number, all we need to check is whether its a number or not, so will use "isnumber" and will gives result of "True" or "False"

3. And the last, use filter to show up all doctor name that has "True" as a result.

Sample in a simple way, let's assume the Age category has been divided to (Child, Teen, and Oldster)

Sheet Filter

And list of the sample doctor is as below;

Sheet List Doctor

and now, lets test the category from the sheet filter to each doctor, the formula should be put in the same row of the doctor as we will use filter later, in this case the test formula is written in cell E2.

FIND = is used to search a specific text in a cell, the return is the number starting position of the text.

whatever the number result later, The point is, if the specific text are in the cell, it will shows number.

and so, lets check the filter in category 1, insurance

=Find(find "Run", in B2 of sheet list doctor)

=(FIND(Filter!$B$1,'List Doctor'!B2)

continue check the filter in category 2, age

=Find(find "Oldster", in C2 of sheet list doctor)

and so on.

then total all the test result. if all match it will shows number, if some can't find the text it will shows error.

Then, we will use ISNUMBER to check that.

ISNUMBER = to check whether the value in a cell, is a number or not.

then the formula will be as below;

ISNUMBER(FIND(Filter!$B$1,'List Doctor'!B2)+FIND(Filter!$B$2,'List Doctor'!C2)+FIND(Filter!$B$3,'List Doctor'!D2))

The doctor that match all category will show true as result.

Then back to Sheet Filter

FILTER = to show some table result with the filter we command

The table we want to show are in column A from "Sheet List Doctor" based on the Result filter in column E from "Sheet List Doctor" as well if the result is "True".

And the formula will be as below;

=FILTER('List Doctor'!A:A,'List Doctor'!E:E=TRUE)

Note:

Be careful, the FIND formula is sensitive, different space or caps can makes inaccurate result. To avoid that, is recommended to separate each category and input the category from list data validation.