Summary

To test multiple cells, and return the value from the first non-blank cell, you can use a formula based on the IF function. In the example shown, column D holds task complete dates. The formula in cell F5 is:

=IF(B5<>"",B5,IF(C5<>"",C5,IF(D5<>"",D5,IF(E5<>"",E5,"no value"))))

which returns a value from the first non-blank cell, B5, C5, D5, or E5, respectively. When all cells are blank, the formula returns "no value". The value returned when all cells are blank can be adjusted as desired.

Generic formula

=IF(A1<>"",A1,IF(B1<>"",B1,IF(C1<>"",C1,IF(D1<>"",D1,"no value"))))

Explanation 

In Excel, empty double quotes ("") mean empty string. The <> symbol is a logical operator that means "not equal to", so the following expression means "A1 is not empty":

=A1<>"" // A1 is not empty

This expression is used four times in the formula shown in the example, in order to test four different cells in a particular order.

The overall structure of this formula is what is called a "nested IF formula". Each IF statement checks a cell to see if it not empty. If not empty, the IF returns the value from that cell. If the cell is empty, the IF statement hands off processing to another IF statement:

=IF(B5<>"",B5,IF(C5<>"",C5,IF(D5<>"",D5,IF(E5<>"",E5,"no value"))))

The flow of a nested IF is easier to visualize if you add line breaks to the formula. Below, line breaks have been added to the formula to line up the IF statements:

=
IF(B5<>"",B5,
IF(C5<>"",C5,
IF(D5<>"",D5,
IF(E5<>"",E5,
"no value"))))

With ISBLANK

Excel contains the ISBLANK function, which returns TRUE when a cell is blank:

=ISBLANK(A1) // A1 is blank

The behavior can be "reversed" by nesting the ISBLANK function inside the NOT function:

=ISBLANK(A1) // A1 is not blank

The formula above can be re-written to use ISBLANK as follows:

=IF(NOT(ISBLANK(B5)),B5,IF(NOT(ISBLANK(C5)),C5,IF(NOT(ISBLANK(D5)),D5,IF(NOT(ISBLANK(E5)),E5,"novalue"))))
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.