Is there a coalesce-like function in Excel?

小开

最佳答案

=INDEX(B2:D2,MATCH(FALSE,ISBLANK(B2:D2),FALSE))

This is an Array Formula. After entering the formula, press CTRL + Shift + Enter to have Excel evaluate it as an Array Formula. This returns the first nonblank value of the given range of cells. For your example, the formula is entered in the column with the header "a"

    A   B   C   D
1   x   x   y   z
2   y       y
3   z           z

小开

Or if you want to compare individual cells, you can create a Coalesce function in VBA:

Public Function Coalesce(ParamArray Fields() As Variant) As Variant


Dim v As Variant


For Each v In Fields
If "" & v <> "" Then
Coalesce = v
Exit Function
End If
Next
Coalesce = ""


End Function

And then call it in Excel. In your example the formula in A1 would be:

=Coalesce(B1, C1, D1)

小开

Taking the VBA approach a step further, I've re-written it to allow a combination of both (or either) individual cells and cell ranges:

Public Function Coalesce(ParamArray Cells() As Variant) As Variant


Dim Cell As Variant
Dim SubCell As Variant


For Each Cell In Cells
If VarType(Cell) > vbArray Then
For Each SubCell In Cell
If VarType(SubCell) <> vbEmpty Then
Coalesce = SubCell
Exit Function
End If
Next
Else
If VarType(Cell) <> vbEmpty Then
Coalesce = Cell
Exit Function
End If
End If
Next
Coalesce = ""


End Function

So now in Excel you could use any of the following formulas in A1:

=Coalesce(B1, C1, D1)
=Coalesce(B1, C1:D1)
=Coalesce(B1:C1, D1)
=Coalesce(B1:D1)

小开

Inside the array enter the variables that are not allowed.

Function Coalesce(ParamArray Fields() As Variant) As Variant


Dim v As Variant


For Each v In Fields
If IsError(Application.Match(v, Array("", " ", 0), False)) Then
Coalesce = v
Exit Function
End If
Next
Coalesce = ""


End Function

小开

I used:

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

This tests the if the first field you want to use is blank then use the other. You can use a "nested if" when you have multiple fields.

小开

If you know there will not be any overlap across columns, or want the overlap, then this is a pretty fast way to solve for a coalesce. The below formula does not apply to your values and columns, but rather to my mock-up so you will need to adjust to make it relevant.

=LEFT(TRIM(CONCATENATE(Q38,R38,S38,T38,U38,V38,W38,X38,Y38)),1)

小开

If you only want to coalesce to 0, which is a very common use case, you can simply use the SUM() function around a single value. As a convenience that one treats all blanks as zero, and it's extra convenient since it's so short.

Not a generic solution like the other answers, but a helpful shortcut in many cases where that's exactly what you want.

小开

Depending on how many cells you want to check, you can chain together multiple ISBLANK checks.

For instance, when checking columns A, B, then C:

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

For columns A, B, C, and D:

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

... and so on.

小开

With the updated IFS function in excel you don't need to nest. You can try something like

Create a blank cell to the right Then enter.

=IFS(ISBLANK(A1),B1,ISBLANK(A1),C1,ISBLANK(A1),D1)

Highlight column and paste as needed.

小开

Late to the party and leveraging @AndyMC's answer you can use the following to evaluate vlookups, index(match()) etc. to coalesce your formula statements.

Public Function Coalesce(ParamArray Fields() As Variant) As Variant


Dim v As Variant


For Each v In Fields
        

If Not IsError(v) Then
            

Coalesce = v
            

Exit Function
End If
Next
Coalesce = CVErr(xlErrNA)


End Function

and use it in your Worksheet as followed: =Coalesce(INDEX(SHEET1!$A:$AF,MATCH(Main!$Q36,SHEET1!$I:$I,0),MATCH(Main!D$34,SHEET1!$1:$1,0)),INDEX(SHEET2!A:CR,MATCH(Main!$Q36,SHEET2!$M:$M,0),MATCH("SOMETHING",SHEET2!$1:$1,0)))

For the first statement that does not return #N/A it will return the actual matched value.