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?

Answer 1

try:

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

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.

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.