0

I have a big table in which one of the columns is titled name. I try to use advanced filter to identify the subjects with the exact same names as those on a name list.

The problem is the advanced filter will pick up, for example, "Chris", "Christian" and "Christina", when the list only has Chris and I don't want to have "Christof", "Christina" or "Christian". Similarly, I want only "Jon" and do not want to have "Jonathan" and "Jonna", for example.

It is said that I can use ="=Chris" in the advanced filter criteria. But this seems to work when there is only a single criterion. Now my criteria is a range of specific names, not a single name. I have tried ="=tab2!$A$1:$A$10" but Excel said

the reference isn't valid

".

How could I set up the advanced filter to find the exact match with a range of criteria ?

0

This will solve the issue:

enter image description here

N.B.

  • Criteria should written like ="=Chris" in different Rows (check the Screen Shot).
  • Excel reads Criteria like Chris or John or Ravi if are in one Column.
  • In case if you want to Filter Names only then select only the Name Column as, List Range.
| improve this answer | |
0

If you want to filter based on a list of names that you have stored elsewhere, instead of listing each name separately, you can use a Formula Criteria. (Look that up in Excel Help to understand better). Ensure that your formula is NOT in row 1, and that, if you put a name in row 1, that name does NOT match any column header in your data table. The reference for the formula is the first cell containing data in the relevant column.

The Formula:

=ISNUMBER(MATCH(A5,NameList,0))

This formula will return TRUE or FALSE depending on whether the name in the Names column is found in NameList (and NameList can be on another sheet)

Original Data

enter image description here

Advanced Filter Dialog

enter image description here

Results

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.