Excel Formula to Display Specific Text Based on Two Cell Conditions
Question details
The user needs an Excel formula to output a specific string ('WalkIn') when two specific criteria are met: either both input cells are completely blank, or the second cell contains the specific text 'ZOOM'.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a conditional tracking spreadsheet where an entry needs to be automatically labeled as 'WalkIn' based on the absence of data in two specific columns or a designated meeting type (ZOOM) in the second column.
- Observed behavior
- Requires a functional logical formula combining IF, OR, and AND functions to accurately evaluate the two target cells and output the correct text without resulting in formula errors.
Ensure that the cells you are referencing do not contain hidden spaces, as a cell containing a space character is not considered completely blank by Excel formulas.
Use a Nested IF, OR, and AND Formula
Combine IF, OR, and AND functions to check multiple conditions simultaneously and return the desired text when those criteria are satisfied.
The IF function evaluates a logical test and returns one value if true, and another if false. By nesting OR and AND functions within it, you can instruct Excel to check if both cells A and B are blank, OR if cell B specifically contains your target keyword.
Click on the cell where you want the result to appear (for example, row 6).
Type the following formula into the formula bar: =IF(OR(AND($A6="",$B6=""),$B6="ZOOM"),"WalkIn","")
Press Enter to execute the formula. If the conditions are met, 'WalkIn' will appear; otherwise, the cell will remain blank.
Click and drag the fill handle (the small square at the bottom-right of the selected cell) down your column to copy the formula to additional rows.
Apply Conditional Formulas Easily in WPS Office
WPS Spreadsheet fully supports advanced logical formulas including IF, AND, and OR. You can easily set up conditional text evaluation just like in Microsoft Excel, completely for free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document where you need to apply the conditional formula.
- 2. Input the logic formula: Select your target cell and type =IF(OR(AND($A6="",$B6=""),$B6="ZOOM"),"WalkIn","").
- 3. Drag to apply: Press Enter, then use the fill handle to drag the formula across your entire data range easily.
Frequently Asked Questions
Why does my formula fail when the cell looks blank but actually isn't?
If a cell contains a space character or a hidden apostrophe, the formula will not treat it as blank (""). You can use the TRIM function or clear the cell contents completely to resolve this issue.
How can I make the formula case-sensitive for the word 'ZOOM'?
Standard Excel logical comparisons are mostly case-insensitive. If you want to strictly evaluate exact casing, use the EXACT function. Replace $B6="ZOOM" with EXACT($B6,"ZOOM").
Can I add a third condition to return a different text?
Yes. You can use the IFS function (available in newer versions) or nest another IF statement inside the 'value_if_false' parameter of your current formula to evaluate additional conditions.
What if my cells are in a different row or column?
Simply modify the cell references in the formula. For example, if your data is located in columns D and E starting at row 2, update the formula to =IF(OR(AND($D2="",$E2=""),$E2="ZOOM"),"WalkIn","").
