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 D1 x x y z2 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 FunctionAnd then call it in Excel. In your example the formula in A1 would be:
=Coalesce(B1, C1, D1)