Is there a coalesce-like function in Excel

0 votes

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 by Kithuzzz
• 38,020 points
3,666 views

1 answer to this question.

0 votes
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.
answered Oct 21, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

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

answered Jan 9, 2023 in Others by narikkadan
• 63,720 points
616 views
0 votes
1 answer

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

answered Mar 28, 2023 in Others by Kithuzzz
• 38,020 points
541 views
0 votes
1 answer

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

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
3,547 views
0 votes
1 answer

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

answered Sep 30, 2022 in Others by narikkadan
• 63,720 points
711 views
0 votes
0 answers

Export SQL query data to Excel

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

Aug 18, 2022 in Database by Kithuzzz
• 38,020 points
524 views
0 votes
1 answer

Removing duplicates from Excel rows by adding values of some columns

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

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
832 views
0 votes
1 answer

Xls to csv converter

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

answered Oct 22, 2022 in Others by narikkadan
• 63,720 points
683 views
0 votes
1 answer

Excel viewer lib

 Try http://viewerjs.org. You will need to save your ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,720 points
461 views
0 votes
1 answer

Is there a function to unhide columns in excel through python

Excel file : df.to_excel('demofile.xlsx',index=False) import openpyxl py = openpyxl.load_workbook('demofile.xlsx') exlsheet = ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,720 points
2,134 views
0 votes
1 answer

Is there a function in excel to find duplicates

Solution: You can use Conditional formatting inside the ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 63,720 points
627 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP