Function Problems

Excel Formula to Check if One Cell Contains Two Specific Words

Algirdas Jasaitis Oct 14, 2026 870 views

Question details

The user requires an Excel formula to check a column and return TRUE only if any single cell within that column contains both of two specified reference words simultaneously.

Excel Formula to Return TRUE When One Cell Contains Two Words

Product: Excel
Device & OS: not provided
Scenario: Analyzing a list of text strings where multiple distinct criteria must be found inside the exact same cell to satisfy a logical condition.
Observed behavior: The formula needs to output TRUE if both words appear together in at least one cell, and FALSE if they appear in separate cells or are missing entirely.

Before you start

Verify that your version of Excel or WPS Spreadsheet supports dynamic array functions (such as REDUCE and TEXTSPLIT), or be prepared to use the backward-compatible wildcard method depending on your software version.

Solution 1Recommended

Use Dynamic Array Functions (REDUCE and TEXTSPLIT)

Combine REDUCE, TEXTSPLIT, and COUNTIF to iterate through the cells, split their text, and check if both criteria are present in a single cell.

This advanced method precisely splits the contents of each cell into separate words using a designated delimiter (like a comma or space). It then compares these separated words against your reference list, ensuring the TRUE condition is only met when both words are found together in the same string.

Set up your reference cells

Place the two words you want to search for in a reference range, such as A2:A3 (e.g., 'hello' and 'goodbye').

Identify your target range

Determine the column or range containing the text strings you want to evaluate, such as C2:C6.

Enter the array formula

Select the cell where you want the result to appear and enter the following formula: =SUM(DROP(REDUCE("",C$2:C$6,LAMBDA(a,i,VSTACK(a,1*(SUM(COUNTIF($A$2:$A$3,TOCOL(TEXTSPLIT(i,", "))))>=2)))),1))>=1

Adjust ranges and delimiters

Modify the cell references ($A$2:$A$3 and C$2:C6) to match your actual worksheet data. Update the TEXTSPLIT delimiter (e.g., ", " or " ") based on how words are separated within your target cells.

Use Dynamic Array Functions (REDUCE and TEXTSPLIT)

Formula compatibility: This formula relies on modern LAMBDA and dynamic array functions. If it returns a #NAME? error, your spreadsheet software version may not support these advanced formulas.

Solution 2

Alternative: Use COUNTIFS with Wildcards

If you are checking an entire column and lack dynamic array support, you can use the simpler COUNTIFS function with wildcards.

Advanced Spreadsheet Tools

Handle Advanced Array Formulas with WPS Spreadsheet

WPS Office Spreadsheet provides excellent support for advanced functions, dynamic arrays, and complex data analysis, giving you a smooth and efficient experience when manipulating text strings and logical conditions.

1. Download and Install: Get WPS Office from the official website and install it on your device.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open the file containing your text data.
3. Apply Formulas: Enter the REDUCE, TEXTSPLIT, or COUNTIFS formulas directly into the formula bar to evaluate your cells.
4. Analyze Results: Instantly see the TRUE/FALSE outputs and seamlessly filter or format your data based on these results.

Fully compatible with Microsoft Excel formulas and functions.Supports advanced logical operations and text splitting formulas.Lightweight application that processes large datasets quickly.Free to use with a clean, familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the REDUCE formula return a #NAME? error?

This happens if your spreadsheet software does not support the latest dynamic array functions like LAMBDA, REDUCE, or TEXTSPLIT. Consider updating your software or using the COUNTIFS wildcard method instead.

Can I check for more than two words using this formula?

Yes. By expanding your reference range (e.g., changing A2:A3 to A2:A5) and adjusting the >=2 condition to match the total number of words you are looking for (e.g., >=4), the formula will adapt accordingly.

Does this formula differentiate between exact words and substrings?

The TEXTSPLIT method searches for exact word matches based on your chosen delimiter (like a space or comma), making it very precise. On the other hand, wildcard methods (like COUNTIFS with asterisks) will match substrings, meaning searching for 'hello' would also match the word 'Othello'.

Excel & Data Problems