Menu
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.
651
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 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.
=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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
