Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 651
Fabian Avatar asked Nov 20 '13 18:11

Fabian


People also ask

Is there a coalesce function in Excel?

The Coalesce function evaluates its arguments in order and returns the first value that isn't blank or an empty string. Use this function to replace a blank value or empty string with a different value but leave non-blank and non-empty string values unchanged.

What is rage in Excel?

What is Range in Excel & its Formula? A range is the collection of values spread between the Maximum value and the Minimum value. A range is a difference between the Largest (maximum) value and the Shortest (minimum) value in a given dataset in mathematical terms.


1 Answers

=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 
like image 129
Howard Renollet Avatar answered Sep 21 '22 10:09

Howard Renollet