Get first non-empty cell in row

Question

In Google Sheets, how do I get the value of the first non-empty cell in the row 17 starting at column C forwards?

Withhold

Answer 1

try:

=INDIRECT(ADDRESS(17, INDEX(MIN(IF(C17:17<>"", COLUMN(C17:17), )))))

Loop

This works perfectly on a new spreadsheet. I get "Formula parse error" on an existing doc, where each cell is either empty or a sum of other cells. – Withhold

The issue was due to locale settings where comma , was treated differently. – Withhold

Answer 2

I'm looking at a similar issue and found solutions similar to this, that might work for you:

=INDEX(C17:17,MATCH(TRUE,C17:17<>"",0))

As I understand it, MATCH will find the position of the first element in C17:17 that it's different to "" (exactly, hence the 0) and index will retrieve the value from that same range.

Italianate

Answer 3

I found another way that works, but not nearly as elegant as player0's.

=INDEX( FILTER( (SORT(TRANSPOSE(C17:17),TRANSPOSE(COLUMN(C17:17)),FALSE)) , NOT( ISBLANK( (SORT(TRANSPOSE(C17:17),TRANSPOSE(COLUMN(C17:17)),FALSE)) ) ) ) , ROWS( FILTER( (SORT(TRANSPOSE(C17:17),TRANSPOSE(COLUMN(C17:17)),FALSE)) , NOT( ISBLANK( (SORT(TRANSPOSE(C17:17),TRANSPOSE(COLUMN(C17:17)),FALSE)) ) ) ) ) )

I put this together from two other answers on SO, one on how to reverse the cells in a row, and one on finding the last non-empty cell in a column.

So this formula reverses C17:17, but leaves it as a column:

=(SORT(TRANSPOSE(C17:17),TRANSPOSE(COLUMN(C17:17)),FALSE))

And then this result is used as the range, when finding the last non-blank value in a column, which would be the first non-blank from the original row. (From Get the last non-empty cell in a column in Google Sheets) I replaced A:A in the following, with the formula from just above.

=INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) )

The result is not very pretty but it worked.

Rabelais

Wow that is a big formula. I see it fetched the value only while player0 inherits the format as well. For others who find this answer that might be useful. – Withhold

Answer 4

Forced with computation speed.

The next formula is the most productive:

=MATCH(FALSE;MAP(5:5;LAMBDA(val;ISBLANK(val)));0)

Description: Convert the analysed row to the array with “True” and “False” values. If the cell is not empty -> True, else False. Then find the first “False” element in the array.

1. Function “ISBLANK” is used to check empty cells

   NOT(ISBLANK(val)

2. Function “MAP” applies the “ISBALNK” to each cell in the row and returns an array. MAP(5:5;LAMBDA(val;NOT(ISBLANK(val))))

3. MUTCH finds the index of the first non-empty cell

Ian

Answer 5

My take on this is the following formula (that I defined as a named function COALESCE(range)):

=REDUCE("", range, LAMBDA(accum, item, IF(accum<>"", accum, item)))

Where range is an input argument that can be range or array.

Example usage:

=COALESCE(A1:A10)
=COALESCE({"", 1, 2})
=COALESCE({some_complex_formula(...), another_complex_formula(...), "fallback_value"})

Phillipp

Answer 6

(Similar to OP, I wanted to get the column of the first non-empty cell. Here's what OP wants:)

Solution:

=REDUCE("",C17:17, LAMBDA(acc,x, 
  IF((acc="")*(x<>""), x, acc)       or ,COLUMN(x), or whatever you want
))

---

Explanation

REDUCE is a very basic and extremely useful operation. REDUCE is the functional programming equivalent of a for loop that can only return a single value. In particular, it returns

f(f(f(f(f("",C17), C18), C19), C20), ...)

In our case, the initial 'seed' value we passed as the first parameter is "", and REDUCE iteratively applies (left-to-right, or top-to-bottom) a 2-parameter function f(acc,x) defined as LAMBDA(acc, x, ['return' expr with acc and x as params]) that lets you customize what you want to do.

1. acc -- the intermediate parameter/argument/'variable'

   (we can rename this whatever we want; acc(umulator) is historical, but here we can call it mem or whatever)

2. x -- current cell in the iteration

   (we can rename this whatever we want, like cell or current)

Here for our lambda f says:

"Consider the previous result acc and current cell x.... The variable acc's new value is as follows: If we've seen nothing previously (acc="") and the current cell is non-blank (x<>"") return that; otherwise return what we've seen previously."

Thus, once the REDUCE sees something as it goes left-to-right in the range, it will store it in acc... which sort of clutches onto it permanently and doesn't let it go, nomatter what it sees in the future.

---

. . . P.S.

(to debug what REDUCE is doing: Instead use the function SCAN, which returns the entire range (same size) containing all intermediate computed values of acc, so you can see what is going on.)

(also see MAP, LET, LAMBDA, etc.)

(sidenote: =HLOOKUP("zzz",range,1,true) might work if you trust that it always searches right-to-left, but technically the behavior is undefined in the docs if the array isn't sorted, so you can't rely on that.)

Ormazd