Is there a coalesce-like function in Excel?
Solution 1
=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
Solution 2
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.
Solution 3
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)
Solution 4
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)
Solution 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)
Fabian
Updated on February 15, 2022Comments
-
Fabian over 2 years
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.
-
user2023861 about 6 yearsVery helpful. If you're using this to coalesce the results of a series of vlookups, replace the
isblank
withisna
-
Tim Barrass almost 6 yearsIs there a way of reversing this -- favouring the rightmost column first?
-
Matt Roy over 5 yearsI prefer this solution, this is a more standard formula usage then using an array formula. Thanks.
-
Ryan Sparks over 5 years@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))
-
msciwoj about 5 yearsit shouldn't - it only tests for two fields, imagine you search for first non-empty in a lot more, eg. whole row
-
lanartri over 4 years@MattRoy not so standard since it forces you to save your workbook as XLSB or XLSA
-
Michael Sheaver over 2 yearsI 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.
-
Florian Kusche over 2 yearsSame here, #NAME error. I saved the file as
.xlsm
which did not help. Where do I have to place the code?