Table of Contents
In order to find the first non-zero value in a row using Excel, you can use the “MATCH” function. This function will search for a specified value within a range of cells and return the position of the first cell that contains that value. By using the “MATCH” function with the criteria of “0”, you can locate the first cell in the row that contains a non-zero value. This can be useful for finding specific data within a large dataset or for organizing and analyzing data in a spreadsheet.
Excel: Find First Non-Zero Value in Row
You can use the following formula in Excel to find the first column with a 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 Excel
Suppose we have the following dataset in Excel 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.
The following tutorials explain how to perform other common tasks in Excel:
Excel: A Formula for MID From Right
Excel: How to Use MID Function for Variable Length Strings
Cite this article
stats writer (2024). How can I find the first non-zero value in a row using Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-find-the-first-non-zero-value-in-a-row-using-excel/
stats writer. "How can I find the first non-zero value in a row using Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-find-the-first-non-zero-value-in-a-row-using-excel/.
stats writer. "How can I find the first non-zero value in a row using Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-find-the-first-non-zero-value-in-a-row-using-excel/.
stats writer (2024) 'How can I find the first non-zero value in a row using Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-find-the-first-non-zero-value-in-a-row-using-excel/.
[1] stats writer, "How can I find the first non-zero value in a row using Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I find the first non-zero value in a row using Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.