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.

Oct 20, 2022 in Others 3,186 views

## 1 answer to this question.

To have Excel analyze the formula as an Array Formula after you enter it, hit CTRL + Shift + Enter. The first non-blank value within the specified cell range is returned by this. The formula is typed in the column with the header "a" for your example.
• 63,700 points

## Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

## Is there a function in excel to automatically calculate age using date of birth?

Try  =INT((YEARFRAC(TODAY(),B3,1)))  Where cell B3 contains a date like ...READ MORE

## Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

## Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

## Export SQL query data to Excel

My query returns a huge amount of ...READ MORE

## Removing duplicates from Excel rows by adding values of some columns

Copy the first three columns to another ...READ MORE

## Xls to csv converter

Use xlrd - it's faster, cross-platform, and ...READ MORE