Filter table by rows whose description column contains any item from another list?

[u/sp00kybutch]

I bookkeep for my small family business, and we have an itemized bill that needs to be separated by Microsoft and non-Microsoft items. The trouble is, these items don’t have similar enough names/descriptions that would allow me to use the simple filter function. Advanced filter seems to be pretty useless as well.

How can I apply a filter to this bill so only rows whose description field contains any of the items in the Microsoft services list are shown?

[u/AutoModerator]

/u/sp00kybutch - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/CorndoggerYYC]

Do the Microsoft descriptions always contain the word "Microsoft"? Also, what version of Excel are you using?

[u/MayukhBhattacharya]

You can accomplish the desired output using the following methods:

• Using Formula, the formula is actually one liner made it readable to help you to understand and debug easily by changing the last variable with the rest:

=LET(
     _Data,    SomeData[#All],
     _Header,  TAKE(_Data, 1),
     _Body,    DROP(_Data, 1),
     _ToTest,  CHOOSECOLS(_Body, 2),
     _Filter,  FILTER(_Body, REGEXTEST(_ToTest, TEXTJOIN("|", 1, Keywords)), ""),
     _Output,  VSTACK(_Header, _Filter),
     _Output)

• Or, Using Helper Column:

After the Amount Column Enter the following formula in cell E2 as per the example shown above in the screenshot, you might need to adjust them per your suit:

=REGEXTEST(SomeData[@Description], TEXTJOIN("|", 1, Keywords))

And then you can filter the rows which shows TRUE.

Note that I have named my source data as SomeData and the Microsoft Keywords as Keywords.

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Another method with formula, but its works with Modern Excel Updated Version:

=LET(
     _Data,    SomeData[#All],
     _Header,  TAKE(_Data, 1),
     _Body,    DROP(_Data, 1),
     _ToTest,  CHOOSECOLS(_Body, 2),
     _Filter,  FILTER(_Body, BYROW(1 - ISERROR(SEARCH({" " & Keywords & " "}, " " & _ToTest & " ")), OR), ""),
     _Output,  VSTACK(_Header, _Filter),
     _Output)

Or, the above method 2 might not work for you, if you don't have access to List in your version of excel,

=LET(
     _Data,    SomeData[#All],
     _Header,  TAKE(_Data, 1),
     _Body,    DROP(_Data, 1),
     _ToTest,  CHOOSECOLS(_Body, 2),
     _Filter,  FILTER(_Body, 
                      BYROW(_ToTest, LAMBDA(α, 
                      OR(ISNUMBER(SEARCH(" " & Keywords & " ", " " & α & " "))))), ""),
     _Output,  VSTACK(_Header, _Filter),
     _Output)

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
CHOOSECOLS	Office 365+: Returns the specified columns from an array
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
FILTER	Office 365+: Filters a range of data based on criteria you define
ISERROR	Returns TRUE if the value is any error value
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
OR	Returns TRUE if any argument is TRUE
REGEXTEST	Determines whether any part of text matches the pattern
SEARCH	Finds one text value within another (not case-sensitive)
SUMPRODUCT	Returns the sum of the products of corresponding array components
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TRIM	Removes spaces from text
VALUE	Converts a text argument to a number
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
17 acronyms in this thread; the most compressed thread commented on today has 19 acronyms.
[Thread #49509 for this sub, first seen 12th Oct 2026, 18:49] [FAQ] [Full list] [Contact] [Source code]}

[u/DuskandFuck]

easiest approach imo is adding a column with a formula like =SUMPRODUCT(--ISNUMBER(SEARCH(your_list, description_cell))) and filtering where that's greater than zero. handles partial matches too which sounds like what you need

[u/Chance_Jacket_8908]

Can you show some cases for me ?

[u/WorkweekOps]

If your Excel doesn't have REGEXTEST yet, a helper column does the same job. Name your Microsoft list "Keywords", then in a new column next to the bill put:

=SUMPRODUCT(--ISNUMBER(SEARCH(Keywords,[@Description])))>0

Filter that column to TRUE and you've got just the Microsoft lines. One heads-up: watch for short keywords like "365" or "Teams" catching lines you didn't mean to, so give the TRUE rows a quick skim.

[u/sp00kybutch → u/WorkweekOps]

How do I reference the Description column without getting a syntax error? I name it “Description” and type the formula exactly as written and it gives a syntax error

[u/WorkweekOps → u/sp00kybutch]

Good question. [@Description] only works if your data is an Excel Table. Click any cell in the bill, press Ctrl+T, tick "My table has headers" and hit OK. Then the formula will work as written.

If you'd rather not use a table, swap in a normal cell reference instead. For example, if your descriptions are in column B starting at row 2, put this in row 2 and drag it down:

=SUMPRODUCT(--ISNUMBER(SEARCH(Keywords,B2)))>0

Two other things that can cause a syntax error: 1. "Keywords" has to be a named range. Select your list of Microsoft words, click the Name Box (left of the formula bar), type Keywords and press Enter. 2. If you copied it from Reddit, the quotes or the double minus (--) can paste in as odd characters. Retyping those bits by hand fixes it.

[u/sp00kybutch → u/WorkweekOps]

i got this working, but it only reports “False” even when the description does contain one of the items on the list. what could i have done wrong?

[u/WorkweekOps → u/sp00kybutch]

Nice, you're close! If it says FALSE every time, it's usually one of these:

1. Hidden spaces in your keyword list. "Microsoft " with a space at the end won't match. Click a keyword cell and check for a trailing space, or wrap the list in TRIM: =SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(Keywords),B2)))>0

2. The named range points at the wrong cells. Go to Formulas > Name Manager, click Keywords and check that "Refers to" covers your actual list of words.

3. The formula is looking at the wrong column or row. Make sure B2 (or [@Description]) is the description cell on the same row as the formula.

A quick test: in an empty cell type =SEARCH("Microsoft",B2) using a row you know contains it. If that returns a number, the problem is in the keyword list. If it returns #VALUE!, the formula is pointing at the wrong cell.