How to Lambda Contains Which Things in Excel

Why This Task Matters in Excel

Business users regularly face the question, “Which items from this reference list appear in my data?”

• A marketing manager receives free-form survey feedback and wants to know which product names are mentioned in each comment.
• A compliance analyst scans email text to detect banned keywords.
• A sales operations team must match thousands of SKU codes inside customer purchase notes against an official catalog.

Manually checking these situations wastes hours and invites errors. Automated detection quickly turns unstructured text into actionable information: you instantly know which products are popular, which risk words were used, or which SKUs were ordered. Excel is a natural platform because it lets you keep the master list of items, collect the raw text, and build dashboards that summarize results—all in one file.

In modern Microsoft 365, dynamic arrays and the LAMBDA function let you wrap logic in a custom-named function, so every analyst in your organization can type `=CONTAINSWHICH(`...) just like any built-in formula. That increases consistency, reduces formula errors, and shortens development time. Knowing how to create such a reusable function is also a gateway skill for more advanced custom solutions because LAMBDA turns Excel into a lightweight programming environment.

Failing to master this task carries real consequences: your dashboards show incomplete results, compliance gaps remain hidden, and manual processes balloon as data volume rises. In contrast, once you learn to build a “lambda contains which things” solution, you can reuse the pattern for countless other problems—duplication checks, quality control, or competitor monitoring—making you a far more versatile Excel professional.

Best Excel Approach

The most robust approach is to build one reusable LAMBDA-based custom function named CONTAINSWHICH. It takes two parameters:

1. things_list – a vertical or horizontal range or array of search terms
2. text_input – a single text string where you want to look for those terms

Internally the LAMBDA uses the FILTER function to keep only the items whose SEARCH result is a number. SEARCH returns the starting position of one text within another; when the text is not found it spills the error #VALUE!. Wrapping SEARCH inside ISNUMBER converts found/not-found into TRUE/FALSE, which is perfect for FILTER.

=LAMBDA(things_list,text_input,
       FILTER(things_list,
              ISNUMBER(SEARCH(things_list,text_input))))

Why this is best:

• Dynamic arrays let the formula spill an answer of variable length—no need to specify how many items could be returned.
• LAMBDA turns the three-line logic into a one-word function name that anyone can reuse.
• SEARCH is case-insensitive, ideal for most text matching tasks.
• The function works on any size list without modification.

Use this method whenever you have Microsoft 365 or Excel 2021 with LAMBDA support and primarily need case-insensitive substring matching. If you need case-sensitive detection, swap SEARCH with FIND. If you must respect whole-word boundaries, switch to REGEXMATCH (see Alternative Methods).

There is almost no setup requirement beyond having the Things list and a single text cell. Because all references are relative, you can copy the function anywhere.

Alternative quick formula (without naming a LAMBDA)

=FILTER($B$2:$B$20,ISNUMBER(SEARCH($B$2:$B$20,$E2)))

Use this shorthand when you only need the logic in one sheet and do not want to set up a name.

Parameters and Inputs

things_list

• Accepts a contiguous range like [B2:B20] or a hard-coded array such as [\"apple\",\"banana\",\"pear\"].
• Values should be plain text without leading/trailing spaces. Trim if necessary.
• Allowed length: up to the limit of rows in Excel, but practical performance drops if the list exceeds roughly 50 000 items.

text_input

• A single cell, usually a comment, note, or long text.
• Data type: text or number coerced to text.
• Be aware of embedded line breaks; SEARCH can handle them but makes manual reading harder.

Optional parameters (advanced):
You can extend the LAMBDA to include a third argument mode to switch between case-sensitive or whole-word only searches. However, the base version keeps inputs minimal.

Data preparation

• Remove nonprinting characters with CLEAN.
• Use LOWER on both sides if you want to force case insensitivity and later swap to FIND.
• For multi-word items (for example, \"XL-100\"), ensure they exactly match the representation in the text.

Edge cases

• Overlapping patterns—if things_list has \"net\" and \"internet\", both are reported because SEARCH evaluates each independently.
• Empty text_input returns a #CALC! spill error. Wrap with IF(text_input=\"\", \"\", …) if you expect blanks.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose column B ([B2:B6]) lists test keywords:
apple
banana
pear
mango
cherry

Cell E2 contains customer feedback:
\"I really loved the apple pie, and the mango smoothie was excellent!\"

Step-by-step:

1. Create the LAMBDA.
   Formulas ► Name Manager ► New.
   Name: CONTAINSWHICH
   Refers to:

=LAMBDA(things_list,text_input,
       FILTER(things_list,
              ISNUMBER(SEARCH(things_list,text_input))))

2. In F2, enter

=CONTAINSWHICH($B$2:$B$6,E2)

3. Press Enter. Excel spills two cells:
   apple
   mango

Why it works: SEARCH cycles through the five items, returns 13 for apple, #VALUE! for banana, and 54 for mango, etc. ISNUMBER turns those positions into TRUE or FALSE, and FILTER keeps only the TRUE rows.

Common variations:

• Place the text in multiple rows (E2:E20) and copy the formula down. Each row produces an independent spill range.
• Wrap the result in TEXTJOIN(\", \",TRUE,…) to turn the list into one comma-separated string.

Troubleshooting:

• If nothing appears, check for extra spaces in the things list or unexpected capitalization when you replace SEARCH with FIND.

Example 2: Real-World Application

Scenario: A compliance officer must flag emails that contain any prohibited word. The prohibited list in [A2:A50] includes \"confidential\", \"non-public\", \"leak\", \"insider\", etc. Column D houses hundreds of email bodies.

Business logic: You need a quick indicator “Contains sensitive term (Yes/No)” to feed into a Power BI report.

Steps:

1. LAMBDA as before, or if you want a flag, build a second LAMBDA named CONTAINSANY returning TRUE/FALSE.

=LAMBDA(things_list,text_input,
       OR(ISNUMBER(SEARCH(things_list,text_input))))

2. In E2 enter

=IF(CONTAINSANY($A$2:$A$50,D2),"Yes","No")

3. Fill the formula down [E2:E501]. Conditional-format in red where value = \"Yes\".

Integration:

• Combine with FILTER to copy only violating emails to a separate sheet.
• Use Data ► Get & Transform to bring new emails daily, then refresh.
• Hook a pivot table on the Yes rows to count violations per week.

Performance considerations: A 50-word list across 10 000 emails (totalling one million checks) still calculates instantly on a modern PC. If the list grows to thousands, see Alternative Methods for Power Query and VBA options.

Example 3: Advanced Technique

Need: Marketing wants to know which of 300-plus product names appear multiple times in social media posts stored across rows and columns—each row is a tweet, each column B:G holds segmented text fragments.

Challenges:

• Case-sensitive search—the brand “Jet” must not match “jetlag”.
• Whole-word only—the product “XL” should not match “explain”.
• High volume—60 000 tweets.

Solution: Build a REGEX-based LAMBDA that concatenates all cell fragments and uses word boundaries.

=LAMBDA(things_list, rng,
    LET(
        txt, TEXTJOIN(" ",TRUE,rng),
        pattern, TEXTJOIN("|",TRUE,"(?i:\b"&things_list&"\b)"),
        FILTER(things_list, REGEXMATCH(txt, pattern))
    ))

Explanation:

• TEXTJOIN assembles the six fragments into one searchable text.
• The pattern builds \"(?i:\bapple\b)|(?i:\bbanana\b)|...\" meaning \"case-insensitive whole word apple OR banana\".
• REGEXMATCH returns an array of TRUE/FALSE for each thing.
• FILTER extracts the matches.

Professional tips:

• When you have many tweets, calculate in groups to avoid extremely long regex patterns that may hit 32 767-character limits.
• Consider SCAN to progressively process large ranges if memory is tight.
• You can return not just which products were found but how many times by replacing REGEXMATCH with REGEXREPLACE to strip non-matches and then LEN comparisons.

Tips and Best Practices

1. Keep a dedicated “Lookup” sheet where all things lists live. That centralizes maintenance.
2. Always TRIM and LOWER both the list and text when you need case-insensitive matching; it prevents hidden mismatches.
3. Convert lists to Excel Tables so ranges resize automatically when you add new keywords—the LAMBDA can reference Table1[Keyword].
4. If you need a single-cell output, wrap the spill range with TEXTJOIN or use CONCAT to avoid breaking downstream formulas expecting one cell.
5. Document your LAMBDA with the optional third argument “help_text” so hovering shows usage notes.
6. Periodically audit overlaps in your things list—short words embedded in longer words might generate unexpected hits.

Common Mistakes to Avoid

1. Forgetting to name the LAMBDA. Typing `=LAMBDA(`... in a worksheet cell works only for testing; without a Name definition, you cannot reuse it elsewhere. Fix: Create the name in Name Manager.
2. Mixing absolute and relative references. If you copy the formula but references shift, you’ll read the wrong list. Solution: lock lookup ranges with $ or use table names.
3. Leaving trailing spaces in the things list. SEARCH fails because it looks for “apple␠” not “apple”. Prevention: use CLEAN/TRIM or Data ► Text to Columns to remove them.
4. Expecting FIND to behave like SEARCH. FIND is case-sensitive; using it accidentally may hide matches. Recognize the mistake when obvious items are missing and switch back to SEARCH or add LOWER.
5. Returning spills into occupied cells. If the target range has data, you’ll get a #SPILL! error. Clear the obstruction or use TEXTJOIN to compress the output.

Alternative Methods

When to switch:

• If users are on pre-365 Excel, choose COUNTIF or VBA.
• If you need big-data processing, move to Power Query then push results back to a sheet.
• If you must match whole words only, REGEXMATCH with word boundaries is preferred.

Migrating: You can wrap legacy formulas in a new LAMBDA once everyone upgrades—simply redefine CONTAINSWHICH to call the old helper column logic, ensuring a seamless transition.

FAQ

When should I use this approach?

Use it whenever you have a manageable list of search terms (up to tens of thousands) and modern Excel. It is perfect for ad-hoc analysis, dashboards, and recurrent reports where you value transparency over black-box code.

Can this work across multiple sheets?

Yes. Place the things list on Sheet Lookup and text on Sheet Data. In any cell you can point to them:

=CONTAINSWHICH(SheetLookup!$A$2:$A$50,SheetData!B2)

Because a named LAMBDA is workbook-scoped, it does not matter which sheet you call it from.

What are the limitations?

• SEARCH is not whole-word sensitive.
• The function returns duplicate hits if the thing appears twice—you get only one entry per thing.
• Excel limits spill ranges to one million cells; extremely long things lists may overflow.
  Workarounds include wrapping in UNIQUE to remove duplicates and grouping evaluations.

How do I handle errors?

If the text_input is blank, wrap your call with IFERROR or a pre-check:

=IF(LEN(B2)=0,"",CONTAINSWHICH(List,B2))

To trap rare #CALC! errors from an empty FILTER result, wrap in IFNA.

Does this work in older Excel versions?

LAMBDA and FILTER require Microsoft 365 or Excel 2021. In Excel 2016 or earlier, build a helper array:

=IF(ISNUMBER(SEARCH($A$2,$D2)),$A$2,"")

and copy across the list, then use TEXTJOIN to collect results.

What about performance with large datasets?

Up to about 100 000 total tests, calculations are instant. Beyond that, give Excel a manual calculation trigger, or push heavy lifting to Power Query. Keep your things list sorted and de-duplicated; shorter lists equal faster recalculation.

Conclusion

Mastering the “Lambda contains which things” pattern turns Excel into a rapid text-scanning engine. You gain the ability to expose hidden insights—keywords, brands, compliance flags—in seconds, all while keeping formulas readable and reusable. The same techniques underpin countless other tasks, from deduplicating contact names to classifying error codes. Now that you know how to build and extend a CONTAINSWHICH LAMBDA, challenge yourself to add options such as whole-word matching or counts per term, and explore integrating the function into dashboards and Power Query pipelines. Excel’s flexibility is at your fingertips—use it to turn raw text into clear, actionable data.