How to Return a Cell Value Only When Two Excel Cells Are Not Blank
Question details
The user needs an Excel formula that outputs the value from one cell (e.g., A2) strictly on the condition that both it and another referenced cell (e.g., A1) are not empty.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up conditional data retrieval where a result is only displayed if all required input cells contain valid data (text, numbers, or formula results).
- Observed behavior
- The desired formula must treat any data type as valid but return a blank result if either of the evaluated cells is truly empty.
Ensure the cells you are evaluating do not contain hidden spaces or zero-length strings (""), as these might prevent the ISBLANK function from recognizing them as truly empty.
Use the IF, AND, and ISBLANK Functions
This is the most robust method to verify if multiple cells are completely empty before executing a conditional action and returning a value.
By nesting the ISBLANK function inside an AND logical test, you can accurately evaluate the status of multiple cells simultaneously. The NOT function reverses the ISBLANK result, so the formula proceeds only if the cells are explicitly not blank.
Click on the cell where you want the conditional result to be displayed (for example, A3).
Type the formula =IF(AND(NOT(ISBLANK(A1)),NOT(ISBLANK(A2))),A2,"") into the formula bar.
Press Enter. The cell will now display the value of A2 only if neither A1 nor A2 is completely empty. Otherwise, it will remain blank.
Use the COUNTA Function as a Shorter Alternative
The COUNTA function counts non-empty cells, providing a slightly shorter formula to verify if a continuous range of cells contains data.
Effortlessly Manage Logical Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports all standard spreadsheet functions, including IF, AND, NOT, and ISBLANK. You can easily perform complex logical tests and seamless data validation within a free, highly compatible environment.
- 1. Open your file in WPS: Launch WPS Office and open your spreadsheet document.
- 2. Select the formula cell: Click on the target cell where you want to output the conditional value.
- 3. Insert the logic function: Input =IF(AND(NOT(ISBLANK(A1)),NOT(ISBLANK(A2))),A2,"") and hit Enter to apply the calculation seamlessly.
Frequently Asked Questions
Why does my formula return a blank when the cells clearly have data?
Double-check your cell references. Also, ensure the data in the referenced cells hasn't been formatted in a way that interferes with the formula, though ISBLANK typically recognizes any data including text, numbers, and dates.
How can I check if more than two cells are not blank?
You can expand the AND function to include more conditions. For example: =IF(AND(NOT(ISBLANK(A1)), NOT(ISBLANK(A2)), NOT(ISBLANK(A3))), A2, ""). Alternatively, use the COUNTA method: =IF(COUNTA(A1:A3)=3, A2, "").
Can I return a specific text instead of a blank if the condition isn't met?
Yes. Simply replace the empty quotes ("") at the end of the IF formula with your desired text enclosed in quotation marks, such as "Missing Data".
Does ISBLANK treat a formula returning an empty string as blank?
No. If a cell contains a formula that returns an empty string (""), ISBLANK will evaluate it as FALSE because the cell technically contains a formula. In such cases, using =IF(AND(A1<>"", A2<>""), A2, "") is a better approach.
