Reverse Partial Match Lookup Formula to Filter for a List in Excel
25

Reverse Partial Match Lookup to Filter for a List in Excel

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 25 comments
MF - July 28, 2017

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. 🙂

Reply
Kevin Lehrbass - June 18, 2017

Hi Jon,

I created a post to explore a couple of additional questions based on your partial match.
http://www.myspreadsheetlab.com/reverse-partial-match-lookup-to-filter-for-a-list/

Cheers,
Kevin

Reply
Florence Lorenzo - June 16, 2017

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

Reply
KRISH - June 14, 2017

That’s awesome Mr.Jon. Thank you.

Reply
Thomas Dowis - June 13, 2017

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

Reply
Sandeep - June 12, 2017

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

Reply
Philippe - June 11, 2017

Wish me good luck to make this work on Google Sheet ^^

Reply
YasserKhalil - June 10, 2017

That’s awesome and fascinating Mr. Jon
Thanks a lot

Reply
Eswari - June 10, 2017

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

Reply
    Jon Acampora - June 22, 2017

    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! 🙂

    Reply
Rajeev - June 9, 2017

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

Reply
Bernice - June 9, 2017

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

Reply
    Jon Acampora - June 22, 2017

    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. 🙂

    Reply
Douglas - June 9, 2017

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

Reply
Edil - June 9, 2017

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.

Reply
Arun - June 9, 2017

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

Reply
    Jon Acampora - June 9, 2017

    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. 🙂

    Reply
      Arun - June 10, 2017

      Thanks Jon, I will go by your suggestion by filling dummy values to avoid any blank cells in the filter list.

      Reply

Leave a Reply: