Excel If Two Cells Are Blank Then Return Value

Get FREE Advanced Excel Exercises with Solutions!

In this article, we’ll learn some easy and practical Excel techniques for returning comments or values in a third column if two cells are blank.


Excel If Two Cells Are Blank Then Return Value: 2 Formulas

1. IF-AND Formula to Return Comments If Two Cells Are Blank

We will now return various comments based on the preceding two columns’ conditions.

  • A= QC approves this product and payment is done.
  • B=  We can make payment.
  • C=  Immediately sent to QC.
  • D=  Neither QC approved it yet, nor the payment is done.
  • E=  Have to reorder.

📌 Steps:

  • To begin, choose a separate cell E5. Second, in the E5 cell, enter the following formula and drag the fill handle downward.
=IF(AND(C5="Yes",D5="done"),$G$5,IF(AND(ISBLANK(C5),ISBLANK(D5)),$G$8,IF(AND(C5="No",D5="Retrieved"),$G$9,IF(AND(C5="",D5="Done"),$G$7,IF(AND(C5="Yes",D5=""),$G$6)))))

Applying IF-AND Formula to Return Comments If Two Cells Are Blank in Excel

🔎 How Does the Formula Work?

  • AND(C5=”Yes”, D5=”done”)
    The AND function checks whether the logical inputs are all true. If any of them are false, then it returns false. This part of the formula becomes AND(TRUE,TRUE). So it will return TRUE.
    Output: TRUE.
  • =IF(AND(C5=”Yes”, D5=”done”),$G$5, IF(…….
    From the above breakdown, we can rewrite this formula in a simpler form like the following: =IF(TRUE, $G$5, IF(…….
    So, AND would return TRUE, then IF will return $G$5.
    Output: $G$5 (Comment A)
    In case AND returns FALSE, then it will go to the next logic which is again the IF function.
  • ISBLANK(C5)
    The ISBLANK function
    tests whether cell C5 is blank or not and returns TRUE if it is blank, or FALSE if it is not blank.

Read More: How to Return Value If Cell is Blank


2. Formula to Return Value in a Third Column If Two Cells Are Blank

In this approach, we will determine whether or not both cells are blank. If both cells are empty, return the string “Both Are Blank” otherwise skip the cell.

📌 Steps:

  • To begin, choose a separate cell E5. Second, in the E5 cell, enter the following formula and drag the fill handle downward.

Applying Formula to Return Value in a Third Column If Two Cells Are Blank in Excel

=IF(AND(ISBLANK(B5),ISBLANK(C5)),"Both Are Blank","")

🔎 How Does the Formula Work?

  • ISBLANK(B5)
    The ISBLANK function tests whether cell B5 is blank or not and returns TRUE if it is blank, or FALSE if it is not blank.
    Output: TRUE
  • ISBLANK(C5)
    This works just as like as ISBLANK(B5).
    Output: TRUE
  • AND(ISBLANK(B5), ISBLANK(C5))
    The AND function checks if the logical inputs are all true. If any of them are false, then it returns false. This part of the formula becomes AND(TRUE,TRUE). So it will return TRUE.
    Output: TRUE.
  • =IF(AND(ISBLANK(B5), ISBLANK(C5)), “Both Are Blank”,””)
    From the above breakdown, we can rewrite this formula in a simpler form like the following: =IF(TRUE, “Both Are Blank”, “”)
    So, AND would return TRUE, then IF will return the text within the two double quotes, “Both Are Blank”.
    Output: Both Are Blank

Read More: If Cell is Blank Then Show 0 in Excel


How to Apply a Formula If Two Cells Are Not Blank in Excel

We will use this method to detect whether or not both cells are blank first. If both cells are empty, return the string “Both Are Blank,” or else use a formula, such as the AVERAGE function used here to demonstrate.

📌 Steps:

  • To begin, select E5 as a distinct cell. Second, enter the following formula in the E5 field and slide the fill handle downward.

If Two Cells Are Not Blank Then Apply a Formula in Excel

=IF(AND(ISBLANK(B5),ISBLANK(C5)),"Both Are Blank",AVERAGE(B5:C5))

🔎 How Does the Formula Work?

=IF(AND(ISBLANK(B5),ISBLANK(C5)),”Both Are Blank”,AVERAGE(B5:C5))

  • ISBLANK(B5)
    The ISBLANK function tests whether cell B5 is blank or not and returns TRUE if it is blank, or FALSE if it is not blank.
    Output: FALSE
  • ISBLANK(C5)
    This works just as like as ISBLANK(B5)
    Output: FALSE
  • AND(ISBLANK(B5), ISBLANK(C5))
    The AND function checks if the logical inputs are all true. If any of them are false, then it returns false. This part of the formula becomes AND(FALSE,FALSE). So it will return FALSE.
    Output: FALSE.
  • =IF(AND(ISBLANK(B5), ISBLANK(C5)), “Both Are Blank”, AVERAGE(B5:C5))
    From the above breakdown, we can rewrite this formula in a simpler form like the following: =IF(FALSE, “Both Are Blank”, AVERAGE(B5:C5))
    So, this piece of formula will return the second return option of the IF function, which is a formula here. We have used the AVERAGE function just as an example. You could use any of the Excel functions, or any formulas in this part.
    If AND would return TRUE, then IF will return the text within the two double quotes, “Both Are Blank”.
    Output: AVERAGE(B5:C5) (Return of AVERAGE function)

Read More: How to Calculate in Excel If Cells are Not Blank


Download Practice Workbook

You can download the practice workbook from the following download button.


Conclusion

So we have discussed 2 formulas to return a value if two cells are blank in Excel. You are welcome to download the workbook and use it for your practice. If you have any questions, concerns, or suggestions, please leave them in the comments section. Visit our blog for more such Excel-related articles. Have a nice day.


Related Articles


<< Go Back to If Cell is Blank Then | Blank Cells | Excel Cells | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo