Is there a coalesce-like function in Excel?

Excel's coalesce arsenal

Custom VBA function for COALESCE-like behavior

Unleash the power of VBA to forge a custom Coalesce function:

Function Coalesce(ParamArray args() As Variant) As Variant
    Dim cell As Variant
    For Each cell In args
        If Not IsEmpty(cell) Then
            Coalesce = cell
            ' We've struck oil, folks! Time to stop drilling. 😎
            Exit Function
        End If
    Next cell
    Coalesce = "No valid data" ' Cardboard for dinner again... 📦🍴
End Function


Deploy it in Excel as:

=Coalesce(B1, C1, D1)


Taming complex ranges with native Excel functions

Minimize dependencies with Excel functions for defined ranges. The CONCATENATE, TRIM, and LEFT functions will be your allies:

=TRIM(LEFT(CONCATENATE(A1 & " ", B1 & " ", C1 & " "), SEARCH(" ", CONCATENATE(A1 & " ", B1 & " ", C1 & " "))))


This script uses blank spaces as dividers, returning the first non-blank cell.

Applying standard functions for simplicity

For scenarios where even VBA seems overkill, leverage IF and ISBLANK functions to fish out the first non-empty cell:

=IF(ISBLANK(A1), IF(ISBLANK(B1), C1, B1), A1)


Escaping the nested IF maze

When faced with nested IF terror, opt for the exquisite duo of LOOKUP and IFERROR functions:

=LOOKUP(2, 1/(NOT(ISBLANK(A1:F1))), A1:F1)


This clever construct absorbs consecutive blank cells, marking an end to nested complexity.

Navigating the data wilderness

Wide columns? No problem!

Battling wide columns? Sidestep the formula jungle with:

=Coalesce(A1:Z1) 


The above VBA method easily compasses a span of columns.

Evade data overlap pitfalls

The CONCATENATE and TRIM construct risks data overlap. Future-proof your formula with non-characters as delimiters:

=TRIM(CONCATENATE(A1 & CHAR(9), B1 & CHAR(9), C1))


Here, the tab character—rarely used in raw data—safely fences your data.