Excel: If cell contains formula examples

This tutorial demonstrates various Excel formulas to check if a cell contains specific values, including text, numbers, or parts of strings. It covers scenarios using IF, ISTEXT, ISNUMBER, SEARCH, FIND, COUNTIF, EXACT, SUMPRODUCT, VLOOKUP, and nested IF statements.

One common Excel task is identifying cells containing particular values. These values can range from any text or number to specific text strings or simply non-blank cells. Different "If cell contains" formulas exist depending on the value type. Generally, the IF function performs a logical test, returning a value if the condition (cell contains the value) is true, and another if false.

The tutorial covers these scenarios:

• Any Value: Detects any non-blank cell using IF(cell"", value_to_return, "").
• Text: Identifies cells with text using IF(ISTEXT(cell), value_to_return, "").
• Number: Identifies cells with numbers using IF(ISNUMBER(cell), value_to_return, "").
• Specific Text (Exact Match): Finds cells containing precise text using IF(cell="text", value_to_return, ""). Case-sensitive variations use EXACT.
• Specific Text (Partial Match): Locates cells containing a substring using IF(ISNUMBER(SEARCH("*text*", cell)), value_to_return, "") or IF(COUNTIF(cell, "*text*"), value_to_return, ""). Case-sensitive versions employ FIND.
• Multiple Conditions: Handles multiple conditions using nested IF statements or COUNTIF for multiple text searches.
• OR Logic: Identifies cells containing at least one of several strings using OR with ISNUMBER(SEARCH) or more efficiently with SUMPRODUCT(--ISNUMBER(SEARCH(strings, cell)))>0.
• AND Logic: Finds cells containing all specified strings using AND with ISNUMBER(SEARCH) or COUNTIF.
• Different Results Based on Cell Value: Uses nested IF statements, LOOKUP, or VLOOKUP to return varying results based on cell content. LOOKUP requires alphabetically sorted lookup values.

The tutorial provides numerous formula examples with screenshots illustrating the results. It also explains the logic behind each formula and offers alternative approaches for improved efficiency and readability, particularly when handling multiple search criteria. A practice workbook is offered for hands-on learning.

The above is the detailed content of Excel: If cell contains formula examples. For more information, please follow other related articles on the PHP Chinese website!