Table of Contents
Do you ever find yourself needing to locate the first non-zero value in a row in Excel? If so, you’re in the right place! In this tutorial, you’ll learn how to quickly and easily find the first non-zero value in a row using a few simple formulas. We’ll also discuss the various scenarios in which this technique can be used, so that you can get the most out of this powerful Excel feature. So let’s get started!
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.
Excel: A Formula for MID From Right
Excel: How to Use MID Function for Variable Length Strings