Bottom line: Learn how to filter for a list of items using a reverse partial match lookup.
Skill level: Intermediate
Download the Example File
Download the example Excel file to follow along.
Reverse Partial Match Lookup
A few great questions came in from last week's post on 2 ways to filter for a list of multiple items.
Arun and Edil wanted to know how to filter a column for a partial match to any item in the filter list. We want to see if the cell in the data list contains ANY of the words in the filter list.
We can call this a reverse partial match lookup.
In this example we have a list of all the cities in California. We want to search the list of city names to see if each name contains ANY of the words in the filter list (column E). We then want to filter the city list for all partial matches to the filter list.
The LOOKUP and SEARCH Formula Explained
Like everything in Excel, there are a few ways to go about this. In this example we are going to use a formula with the LOOKUP and SEARCH functions.
Here is the formula. It's a bit of a monster. 🙂
=IFERROR(LOOKUP(1E+100,SEARCH(tblReverseList[Filter List],[@City]),tblReverseList[Filter List]),"")
The formula uses the SEARCH function to look for each item in the Filter List within the cell that is referenced in the “within_text” argument. If the SEARCH function finds a match, it returns the character number that the search word (find_text) starts at.
This returns an array (list) of values that looks like the following.
A number is returned in the list where a match is found. In the list above, the 4th item contains a match that starts at the 8th character in the word that is being searched.
The LOOKUP function is then used to find any numbers in the resulting array list from the SEARCH function. The lookup value is “1E+100”. This is scientific notation for a really large number. Since the number that is found will be smaller than that number, the LOOKUP function returns the last number in the list (array).
Since we specified the optional “result_vector” argument for LOOKUP as the filter list, LOOKUP returns the contents of the cell for the 4th item in the list.
The formula is then wrapped in the IFERROR function. If no matches are found, the LOOKUP function will return a #N/A error. The IFERROR function handles the error and allows us to return a blank or other value.
The SEARCH function is NOT case sensitive. This means it doesn't consider upper or lower case letters and will return a match even if the case doesn't match. We can use the FIND function, instead of SEARCH, to do a case sensitive search.
Checkout the video above for further explanation on this formula.
Filter The List for All Partial Matches
We can now apply a filter to column that contains the formula. Since the IFERROR function returns a blank “” if no partial match is found, we can filter out blanks to see all rows that contain a partial match.
We can also just return a TRUE or FALSE as the result of the formula to make filtering easier. To do this we just add the following to the end of the formula.
That means Does Not Equal Blank. So the full formula looks like the following.
=IFERROR(LOOKUP(1E+100,SEARCH(tblReverseList[Filter List],[@City]),tblReverseList[Filter List]),"")<>""
Now we can filter the column for TRUE to see all the cities that contain ANY of the words from filter list.
Group Items for Summary Reports and Pivot Tables
The column with the LOOKUP and SEARCH formula can also be used to group items in a pivot table. By adding the Partial Match field to the Rows area of the pivot table, we can quickly see a report of the total number of cities in California that contain one of the words in the filter list.
Check Out The Filters 101 Course
The Filters 101 Online Course contains over 40 video lessons just like this one. This is step-by-step training that will help you get the most out of the sort & filter features in Excel, to prepare & analyze your data faster.
How will you use this technique? Please leave a comment below with any suggestions or questions. Thank you! 🙂