Table of Contents
Google Sheets has a built-in function called “INDEX” that allows you to find the first non-zero value in a row. First, select the row where you want to find the first non-zero value. Then, use the following formula in a cell: =INDEX(A1:Z1, MATCH(TRUE, A1:Z1>0, 0)). This formula will search through the selected row and return the first value that is greater than zero. It uses the “MATCH” function to match the criteria (in this case, a value greater than zero) and the “INDEX” function to retrieve the value at that specific location. This allows you to quickly find and extract the first non-zero value in a row in Google Sheets.
You can use the following formula in Google Sheets to find the first non-zero value in a particular row:
=INDEX(B$1:E$1,MATCH(TRUE,INDEX(B2:E2<>0,),0))
This particular formula finds the first value in the row B2:E2 with a non-zero value and returns the corresponding column name from the row B1:E1.
The following example shows how to use this formula in practice.
Example: Find First Non-Zero Value in Row in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the number of fouls by a basketball team in each quarter of eight different games:
Suppose we would like to find the first quarter in which a foul occurred in each game.
In other words, we would like to find the first non-zero value in each row and return the corresponding column name.
We can type the following formula into cell F2 to do so:
=INDEX(B$1:E$1,MATCH(TRUE,INDEX(B2:E2<>0,),0))
We can then click and drag this formula down to each remaining cell in column F:
Column F now displays the first quarter with a non-zero value in each row.
For example, in Game 1 the first foul occurred in Quarter 3 so cell F2 returns a value of Quarter 3:
Note: If every value in a given row is zero then this formula will simply return #N/A since no non-zero value could be found.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: