Bottom line: Learn how to filter for a list of items using a reverse partial match lookup.

Skill level: Intermediate

Reverse Partial Match Lookup to Filter for a List

Video Tutorial

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the Example File

Download the example Excel file to follow along.

Reverse Filter List Of Items - Partial Match.xlsx (43.5 KB)

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.

Reverse Partial Match Lookup to Items in a List

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.

Evaluate Formula Window for LOOKUP and SEARCH formula

This returns an array (list) of values that looks like the following.

{#VALUE!;#VALUE!;#VALUE!;8;#VALUE!;#VALUE!;}

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.

SEARCH Returns Starting Character Number to the Array - Evaluate Formula Window

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.

Formula Returns Partial Match Reverse Lookup

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.

Filter Out Blanks on Partial Match Column

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.

Group and Summarize Partial Match Data with Pivot Table

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.

Filters 101 Course Quick Overview 2 640x360

Click here to learn more about The Filters 101 Course

How will you use this technique?  Please leave a comment below with any suggestions or questions.  Thank you! 🙂

26 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon,
    Nice formula!
    With slight modification, we can do it in a non-formula approach – Advanced Filter.
    Thanks for your inspiration for me to write a blogpost about Advanced Filter. Will share with you later here.
    Happy Friday. 🙂

  • Hi Jon,

    How can I create a macro that will search for a partial match then copy the results to a new worksheet?

    Thank you,
    Florence

  • Hi Jon,

    Great formula that I’m already using.

    One question, if a city has more than 1 name from the filter list, for example “Bay City” then the formula returns the first item only, “Bay”. Is there a way to return and count both?

    Tom

  • Hi,

    It’s really nice! whereas while trying i found one more way out to resolve the query and i.e =IF(OR(RIGHT(F2,3)=$J$2,RIGHT(F2,4)=$J$3)=TRUE,F2,””)

    F column has complete list of cities and J2 & J3 are the cells with the suffix of city names(i have considered only two suffix in place of multiple like you)

    Your suggestion on this shall be highly appreciated!

    Thank You

  • Hi Jon,

    I have another way of doing it. I mean parse the list with space delimiter and you get all the city, hills values into separate columns without any lookup. And just filter what you need. I think this also does the same job without formula.

    Any comments please.

    Best regards
    Eswari

    • Hi Eswari,

      You are still going to need an additional column with a formula to filter for all rows that contain a match. It will also be difficult to use that method in a pivot table filter, as I explain in the video.

      Thanks for the suggestion though! 🙂

  • Hi Jon,
    Kindly tell how to hide and secure our formula in excel which can not visible or edit without permission??

  • Hi Jon, Why is it that the SEARCH function on its own will only find the value that directly corresponds to the value on the same row (even if that whole table is referenced), yet when used in conjuntion with LOOKUP, it searches through the entire list to get a match. I don’t see where the LOOKUP function changes how the SEARCH function works as it is just a parameter of the LOOKUP function. Example – your search list, in order is City, Bay, Beach, Hills, Valley, Wood and the City names are Adelanto, Agoura Hills, Alameda, Albany, Alhambra, Aliso Viejo. You will never get any results from those five Cities because the filter list item is not on the same row. If you add the word City of Adelanto, you will get a result.
    Thanks Bernice

    • Hi Bernice,
      Great question! The LOOKUP function converts the SEARCH function results to an array formula. Typically we would have to press Ctrl+Shift+Enter with the SEARCH function to perform this type of search on multiple cells. However, the LOOKUP function creates the array for us in the lookup_vector argument.

      I hope that helps. 🙂

  • Hello Jon,

    Thanks for your great Excel help. I have been working with the example you gave in the video today, but I cannot make the Search function use multiple criteria in the find_text location (the Filter List.) Excel 2010 is the version I’m on; does this require the 2013 or 2016 release?

    Sincerely,
    Douglas Phelps

  • Hi Jon,

    Simply awesome, your answer and explanation to my query.
    I tried it out and “VOILÁ”, there are all the records I needed.

    Thank you very much.

  • Hi Jon,

    Perfect and thanks a ton! Sincerely appreciate you taking time to add this new blog in response to my query in the previous edition.

    This is exactly what I was looking for and is working with me. There is only one challenge which I am facing. The formula is throwing false results in case there are empty spaces in filter list (ie empty cell between E2 to E7). Any solution for this also..

    Arun

    • Hi Arun,
      Thanks! I’m happy to hear it helped.

      You will probably want to remove any blank (empty) cells from the Filter List. If you are. Otherwise the formula will return a zero (0). You could handle the zero by adding

      <>0

      to the end of the formula. That will give you a column of TRUE/FALSE values.

      But, it might make more sense to just delete the blank cells from the filter list if possible.

      I hope that helps. 🙂

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar