To return the first non-blank value in a row or column, you can use Query or an Index-Match combination formula in Google Sheets.

I have a row with several values, and in that, the first few cells are blank. I want to get the first non-blank value in that row. How to do that?

Note:- Please do take note of the Screenshot numbers given below. I may refer to it in the example part later.

Can I find the first non-blank value in a column similarly?

Yes! We can do that easily in Google Sheets.

Here are the solutions to return the first-non blank value in a row or column in Google Sheets.

I am providing you with the solutions using Query as well as the Index and Match combined formula.

How to Return First Non-blank Value in a Row or Column

Let me start with the solution in which the data/list is row-wise.

Return First Non-blank Value in a Row in Google Sheets

I am beginning with Query as I believe it doesn’t require any explanation to understand the usage.

A) Query Formula That Skips Blank Cells in a Row

=QUERY(transpose(B2:G2),"Select Col1 where Col1 is not null limit 1",0)

Result: $3,855.00

Please refer the Screenshot # 1.

Since our data is in a row, we should first transpose it column-wise to use it in Query.

But I did the same within Query using the function TRANSPOSE.

Now let’s see how to return the first non-blank value using the Index-Match in Google Sheets.

B) Index-Match Formula That Skips Blank Cells in a Row

=index(B2:G2,MATCH(FALSE,ISBLANK(B2:G2),0))

It will also populate the above same result.

How does this formula return the first non-blank cell value in a row range?

Note:-

You may wrap both the above Query as well as the Index-Match formulas with the IFERROR function.

Otherwise, if all the cells are blank, it would return #N/A.

You May Like: Different Error Types in Google Sheets and How to Correct It.

Formula Explanation:

The ISBLANK formula returns the Boolean FALSE wherever there are values in the range, else TRUE.

The MATCH formula matches FALSE in this virtual range and returns the relative position of the first FALSE value.

As per the above example, the result would be 3.

Note:-

If you use the above MATCH and ISBLANK combination for testing without Index, use ArrayFormula with them.

=ArrayFormula(MATCH(FALSE,ISBLANK(B2:G2),0))

The Index itself is an array formula. So within it, the above combo doesn’t require the ArrayFormula.

The value 3 is the offset column number in the Index.

The Index formula would offset zero rows and three columns to return the output.

In short, you can read the formula below.

=INDEX(B2:G2,3)

Return First Non-blank Value in a Column in Google Sheets

Here both the formulas are almost the same as above.

Just change the data reference range, and also, you should remove the TRANSPOSE in Query as this time the data itself is in a column.

Please refer the Screenshot # 2 and see this formula.

A) Query Formula That Skips Blank Cells in a Column

=QUERY({B2:B13},"Select Col1 where Col1 is not null limit 1",0)

Result: $5000.00

Use this Query to return the first non-blank value in a column in Google Sheets.

Here is the Index-Match alternative.

B) Index-Match Formula That Skips Blank Cells in a Column

=index(B2:B13,MATCH(FALSE,ISBLANK(B2:B13),0))

It is a copy of the earlier Index-Match combo. The only difference in this formula is the range which is now a column.

Conclusion

You can use any of the above combinations to return the first non-blank value in a row or column in Google Doc Sheets.

I have written this post as a base for one of my coming Google Sheets tutorials.

In that, I am going to use it in Vlookup to skip blank values in the output.

I’ll put the link under “Related Reading” once that tutorial is ready. Thanks for the stay. Enjoy!

Related Reading:

• Index Match – Better Alternative to Vlookup and Hlookup in Google Sheets.
• How to Get Rows Excluding Rows with Any Blank Cells in Google Sheets.
• Move Index Column If Blank in Vlookup in Google Sheets.
• Filter Vlookup Result Columns in Google Sheets (Formula Examples).
• Vlookup to Get the Last Non-blank Value in a Row in Google Sheets.
• Find the Last Non-Empty Column in a Row in Google Sheets.
• Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel.
• Get the Headers of First Non-blank Cell in Each Row in Google Sheets – Array Formula.