Is there a coalesce-like function in Excel?

Question

I need to fill a cell with the first non-empty entry in a set of columns (from left to right) in the same row - similar to coalesce() in SQL.

In the following example sheet

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

I want to put a cell function in each cell of row A such that I will get:

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

I know I could do this with a cascade of IF functions, but in my real sheet, I have 30 columns to select from, so I would be happy if there were a simpler way.

Howard Renollet · Accepted Answer · 2014-12-16T22:01:38.120

71

=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

edited Dec 16 '14 at 22:01

answered Nov 20 '13 at 18:43

Howard Renollet

4,609
1
24
31

2

Very helpful. If you're using this to coalesce the results of a series of vlookups, replace the `isblank` with `isna` – user2023861 Apr 12 '18 at 14:36
Is there a way of reversing this -- favouring the rightmost column first? – Tim Barrass Sep 14 '18 at 15:40
2

@TimBarrass you probably don't need this anymore, but try this for right to left: =INDEX(B2:D2,1+SUM(IF(B2:D2=B2:D2,1,0))-MATCH(FALSE,INDEX(B2:D2,1,N(IF({1},SUM(IF(B2:D2=B2:D2,1,0))+COLUMN(B2)-COLUMN(B2:D2))))&""="",FALSE)) – Ryan Sparks Mar 11 '19 at 15:19
Worked well for me thanks. I just had to use =INDEX(B2:D2,MATCH(FALSE,B2:D2="",FALSE)) because my blanks were blanks from a preceding function / formula. – Renier Wessels Jul 04 '23 at 05:49

score 37 · Answer 2 · edited Feb 03 '19 at 03:02

37

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.

edited Feb 03 '19 at 03:02

Nick

138,499
22
57
95

answered Feb 03 '19 at 02:59

neil collins

387
3
4

1

it shouldn't - it only tests for two fields, imagine you search for first non-empty in a lot more, eg. whole row – msciwoj May 10 '19 at 10:56

score 17 · Answer 3 · answered Aug 31 '17 at 21:21

17

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)

answered Aug 31 '17 at 21:21

AndyMc

377
3
6

1

I prefer this solution, this is a more standard formula usage then using an array formula. Thanks. – Matt Roy Nov 08 '18 at 21:30
1

@MattRoy not so _standard_ since it forces you to save your workbook as XLSB or XLSA – iDevlop Jan 22 '20 at 15:15
1

I am a complete noob for VBA, but how do I run the function once I have it entered? When I try using it, it gives me a #NAME error in the cell formula. – Michael Sheaver Feb 23 '22 at 20:53
Same here, #NAME error. I saved the file as `.xlsm` which did not help. Where do I have to place the code? – Florian Kusche Feb 27 '22 at 11:35

score 7 · Answer 4 · answered Sep 06 '17 at 04:54

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)

Aaron Rainey · Answer 5 · 2019-10-16T19:48:44.560

5

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)

edited Oct 16 '19 at 19:48

answered Oct 16 '19 at 19:34

Aaron Rainey

51
1
2

score 3 · Answer 6 · edited Feb 14 '22 at 18:22

3

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.

edited Feb 14 '22 at 18:22

Jack Deeth

3,062
3
24
39

answered Aug 03 '21 at 14:11

Arron D

31
2

score 2 · Answer 7 · answered Mar 27 '21 at 02:51

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.

score 0 · Answer 8 · answered Dec 15 '17 at 13:39

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

score 0 · Answer 9 · answered Jul 22 '21 at 13:13

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.

score 0 · Answer 10 · answered Jul 30 '22 at 08:42

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.

Is there a coalesce-like function in Excel?

10 Answers10

Linked