Are you struggling to find a way to return the first non-blank value in a row or column in Google Sheets? Look no further! In this article, I will show you how to achieve this using two methods: Query and an Index-Match combination formula.
Return First Non-blank Value in a Row in Google Sheets
Let’s start with the solution for data arranged row-wise.
A) Query Formula That Skips Blank Cells in a Row
To use this formula, simply transpose the row-wise data to make it column-wise within the Query function. Here’s the formula:
=QUERY(transpose(B2:G2),"Select Col1 where Col1 is not null limit 1",0)
The formula will return the first non-blank value in the row, which in this case is $3,855.00.
B) Index-Match Formula That Skips Blank Cells in a Row
Another method to achieve the same result is by using the Index-Match formula. Here’s how:
=index(B2:G2,MATCH(FALSE,ISBLANK(B2:G2),0))
This formula will also give you the same result as the previous method.
Return First Non-blank Value in a Column in Google Sheets
If you want to find the first non-blank value in a column, you can use similar formulas with a few adjustments.
A) Query Formula That Skips Blank Cells in a Column
Remove the transpose function from the Query formula. Here’s the modified formula:
=QUERY({B2:B13},"Select Col1 where Col1 is not null limit 1",0)
The formula will return the first non-blank value in the column, which in this case is $5000.00.
B) Index-Match Formula That Skips Blank Cells in a Column
Similarly, adjust the range to reflect the column data. Here’s the formula:
=index(B2:B13,MATCH(FALSE,ISBLANK(B2:B13),0))
The result will be the first non-blank value in the column.
Conclusion
You now have two methods to return the first non-blank value in a row or column in Google Sheets. Whether you choose to use the Query formula or the Index-Match combination formula, both will give you the desired result.
If you’d like to learn more about using this in Vlookup to skip blank values in the output, stay tuned for my upcoming tutorial on Google Sheets. In the meantime, feel free to explore more related articles on Crawlan.com to expand your knowledge.
Happy Google Sheets-ing!
[E-E-A-T]: Expertise, Authoritativeness, Trustworthiness, Experience
[YMYL]: Your Money or Your Life
