Formula Errors

How to Use Excel IF Formula to Exclude Blank Cells from Multiple Comparisons

Adam Davis Oct 21, 2026 869 views

Question details

The user needs an Excel formula that returns a specific value (like 'Y') when corresponding cell values match, while tolerating or ignoring blank values in related date and amount cells.

How to Use Excel IF Formula to Exclude Blank Cells from Multiple Comparisons

Product: Excel
Device & OS: not provided
Scenario: Comparing multiple columns of data where some cells might be legitimately empty, requiring a conditional formula that accounts for both exact matches and acceptable blank cells.
Observed behavior: The goal is to successfully output 'Y' if all specified comparisons either match exactly or if the designated related cells are blank, and output 'N' if the conditions are not met.

Before you start

Identify the exact cell references for your comparisons and determine which specific columns represent the data (like dates or amounts) that are allowed to be blank.

Solution 1Recommended

Use Nested AND and OR Functions Inside the IF Formula

Combine the IF function with AND and OR logic to check if cells either match exactly or are blank, returning a specified output based on the result.

By nesting OR functions inside an AND function, you can evaluate multiple pairs of conditions simultaneously. Each OR function checks a specific pair: it returns TRUE if the values match OR if the targeted cell is empty. The AND function then ensures that all these individual groups of comparisons evaluate to TRUE before the IF function ultimately returns your desired 'Y' result.

Select the target cell

Click on the cell where you want the final comparison result to appear.

Enter the nested formula

Type the formula structure: =IF(AND(OR(B3=H3,G3=""),OR(D3=J3,I3=""),OR(F3=L3,K3="")),"Y","N") into the formula bar.

Adjust the matching references

Modify the pairs B3=H3, D3=J3, and F3=L3 in the formula to reflect the actual cell references you are comparing for an exact match.

Adjust the blank cell references

Modify G3="", I3="", and K3="" to point to the specific cells in your worksheet that are allowed to be blank.

Apply and fill down

Press Enter to apply the formula. Click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of your data rows.

Use Nested AND and OR Functions Inside the IF Formula

Customizing Output Values: You can change 'Y' and 'N' in the formula to any other text, such as 'Match' and 'Mismatch', depending on your reporting requirements. Just make sure the text remains enclosed in double quotes.

Advanced Spreadsheet Features

Effortlessly Handle Conditional Formulas in WPS Office

WPS Spreadsheet provides robust support for complex logical functions like IF, AND, and OR. Its intuitive formula editing interface makes it incredibly easy to build, debug, and apply multi-condition formulas to large datasets seamlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing the data you want to compare.
2. Start your formula: Select the target cell and type '=' to trigger the formula input.
3. Insert logical functions: Type 'IF' and use the built-in formula suggestions to easily nest your 'AND' and 'OR' conditions.
4. Select cell references: Utilize WPS Spreadsheet's intelligent cell selection to click and add your references effortlessly instead of typing them manually.
5. Apply to data range: Press Enter to confirm, and double-click the fill handle to automatically copy the formula down your entire column.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Intuitive formula prompts and color-coded brackets for error-free nesting.Handles large datasets smoothly without lagging or crashing.Free and lightweight alternative to Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my IF formula return an error when checking for blanks?

Errors often occur due to missing parentheses or incorrect quotation marks. Ensure you use two double quotes ("") with no space between them to represent a blank cell, and verify that all opened brackets for your AND and OR functions are properly closed.

Can I use ISBLANK instead of empty quotes to check for blank cells?

Yes, you can substitute a check like G3="" with ISBLANK(G3). Both methods effectively evaluate whether a cell is empty. However, note that ISBLANK will return FALSE if the cell contains a hidden formula that returns an empty string, whereas G3="" will still count it as a blank.

How do I add more conditions to this nested formula?

You can add more OR statements inside the AND function. Simply add a comma after the last OR condition before the closing bracket of the AND function, and insert your new condition, such as OR(M3=N3,M3="").

Excel & Data Problems