[SOLVED] Is there a coalesce-like function in Excel?

Is there a coalesce-like function in Excel?

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.

Solution

=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