Formula Errors

How to Return OK, Not OK, or Blank Based on Two Cells in Excel

Aamir Naveed Akram Oct 9, 2026 872 views

Question details

The user needs an Excel formula to evaluate two adjacent cells and return specific text based on conditional matching, including handling completely empty rows.

How to Return OK, Not OK, or Blank Based on Two Cells in Excel

Product: Excel
Device & OS: not provided
Scenario: Evaluating two data entry cells to validate specific expected values (e.g., 4 and 30) while cleanly hiding the result if both input cells are blank.
Observed behavior: The user needs a custom conditional result consisting of 'OK', 'Not OK', or a blank output, rather than standard TRUE/FALSE boolean results.

Before you start

Identify the two specific cells you want to evaluate (e.g., A2 and B2) and ensure you have basic familiarity with Excel's IF and AND functions before building the nested formula.

Solution 1Recommended

Use a Nested IF Formula with AND and COUNTA

This approach combines multiple logical functions to accurately check the specific numbers while detecting if the cells are blank to prevent unwanted 'Not OK' results.

A standard IF function only evaluates one condition. By nesting a second IF function and utilizing AND and COUNTA, you can create a three-tier logic gate: one for the exact match, one for blank cells, and a default for everything else.

Select the destination cell

Click on the cell where you want the validation result to appear (for example, cell D2).

Enter the nested formula

Type the following formula exactly: =IF(AND(A2=4,B2=30),"OK",IF(COUNTA(A2:B2)=0,"","Not OK"))

Apply the formula

Press the Enter key on your keyboard to calculate the result for the first row.

Copy the formula down the column

Click the small square at the bottom-right corner of cell D2 and drag it down to apply the conditional check to the remaining rows in your dataset.

Use a Nested IF Formula with AND and COUNTA

How the formula works: The AND(A2=4,B2=30) segment strictly checks if both values match your requirement. COUNTA(A2:B2)=0 verifies if both cells are completely empty to output a blank (""). If neither condition is met (e.g., one cell has a wrong number or is empty while the other is filled), it defaults to "Not OK".

Advanced Formulas Made Easy

Use WPS Spreadsheet for Complex Data Validation

WPS Spreadsheet fully supports all standard mathematical and logical functions, including nested IF statements. You can easily manage and validate your data with an intuitive interface.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the values you want to evaluate.
2. Input the validation formula: Select your target cell and enter: =IF(AND(A2=4,B2=30),"OK",IF(COUNTA(A2:B2)=0,"","Not OK"))
3. Drag to fill: Hit Enter, then use the fill handle to seamlessly drag the formula down through your entire column.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx files.Intuitive formula builder with syntax highlighting to prevent typing errors.Free, lightweight, and fast alternative for robust daily data analysis.

Frequently Asked Questions

Why does my nested IF formula return an error?

This usually happens due to missing parentheses, misplaced commas, or missing quotation marks around text strings like "OK". Ensure that commas correctly separate the logical test, true value, and false value, and that every opened parenthesis has a matching closed parenthesis at the end.

Can I change the target values in the formula dynamically?

Yes. Instead of hardcoding numbers like 4 and 30 directly into the formula, you can reference other cells (for example, $E$1 and $F$1). This allows you to update the required match conditions without having to edit the formula itself.

How do I modify this formula to check for text instead of numbers?

If you are checking for specific words instead of numbers, you must wrap the target text in double quotes inside the AND function. For example: AND(A2="Yes", B2="Approved").

Excel & Data Problems