How to Use Excel Formula to Find First Value Besides 0?

Sharing is caring!

If you work with data in Microsoft Excel, you may sometimes need to locate the first non-zero value in a range of cells. For example, you might have a spreadsheet tracking inventory, and want to quickly identify the first item that is in stock. Or you could have a list of sales figures and need to find the first month where revenue was generated.

Fortunately, Excel provides a straightforward formula to find the first value besides 0 in a dataset. In this article, we’ll walk through how to use this useful function step-by-step.

Understanding the LOOKUP Function

The key to finding the first non-zero value in Excel is the LOOKUP function. This versatile tool allows you to search for a specific value in a row or column, and return a corresponding value from the same position in a different row or column.

The basic syntax for the LOOKUP function is:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value: The value to search for. In our case, this will be the number 0.
• lookup_vector: The one-dimensional range to examine for the lookup_value. This is where we will specify our range of data.
• result_vector: [Optional] The one-dimensional range to return a result from. If omitted, the lookup_vector is used as the result_vector.

To find the first value besides 0, we will use LOOKUP in combination with some additional functions. But first, let’s look at a simple example.

A Basic LOOKUP Example

Imagine you have the following data in columns A and B:

To find the first color in column B associated with a non-zero number in column A, you would use this formula:

=LOOKUP(2,1/(A2:A5<>0),B2:B5)

Here’s how it works:

1. (A2:A5<>0) compares each value in A2:A5 to see if it is not equal to 0. This returns an array like {FALSE;FALSE;TRUE;TRUE}.
2. 1/(…) turns the TRUE/FALSE values into 1s and 0s, giving us {0;0;1;1}. This is necessary because LOOKUP will match the largest value less than or equal to lookup_value.
3. LOOKUP searches this array for the largest value less than or equal to 2 (since 2 is larger than the largest value, it will match the first 1) and returns the corresponding value from B2:B5. So it returns “Green”.

Adapting the Formula to Find First Value Besides 0

Now that we understand the core LOOKUP functionality, let’s adapt it to find the first non-zero value itself, rather than an associated value.

The formula to find the first value besides 0 in range A2:A10 is:

=LOOKUP(2,1/(A2:A10<>0),A2:A10)

The only difference from the previous example is that we use A2:A10 for both the lookup_vector and the result_vector. This way, LOOKUP returns the actual non-zero value.

However, there is one potential issue with this method. If all values in the range are 0, it will return 0 (the last value in the range). To make the formula more robust, we can wrap it in an IF statement to check for that scenario:

=IF(COUNT(A2:A10)=COUNTIF(A2:A10,0),”No non-zero values”,LOOKUP(2,1/(A2:A10<>0),A2:A10))

• COUNT(A2:A10) counts the total number of values in the range.
• COUNTIF(A2:A10,0) counts the number of 0 values in the range.
• If all values are 0, the IF statement returns “No non-zero values”. Otherwise, it proceeds with the LOOKUP function.

Handling Errors in the Formula to Find First Value Besides 0

In some cases, you may encounter a #DIV/0! error when using this formula. This happens if the first value in the range (in our example, A2) is non-zero. The 1/(…) part ends up dividing by 0 for that cell.

To fix this, we can use the IFERROR function to catch the error and return the first value directly:

=IFERROR(IF(COUNT(A2:A10)=COUNTIF(A2:A10,0),”No non-zero values”,LOOKUP(2,1/(A2:A10<>0),A2:A10)), A2)

Now if the formula would throw a #DIV/0! error, it simply returns the value in A2 instead.

Variations of the Formula

The basic formula can be adapted to find the first value meeting other criteria besides being non-zero. Here are a few examples.

Finding the First Value Greater Than or Less Than X

To find the first value greater than X, use:

=LOOKUP(2,1/(A2:A10>X),A2:A10)

To find the first value less than X, use:

=LOOKUP(2,1/(A2:A10<X),A2:A10)

Replace X with the value you want to compare against.

Finding the First Non-Blank Value

To find the first cell in a range that is not blank, use:

=LOOKUP(2,1/(A2:A10<>””),A2:A10)

This compares each cell to an empty string (“”) to check if it contains any text.

Finding the First Text or Non-Text Value

To find the first cell containing text, use:

=LOOKUP(2,1/(ISTEXT(A2:A10)),A2:A10)

To find the first cell not containing text, use:

=LOOKUP(2,1/(NOT(ISTEXT(A2:A10))),A2:A10)

The ISTEXT function checks if a cell contains text. The NOT function inverts the results, so it finds the first cell that does not contain text.

More Advanced Lookup Formulas

While the formulas covered so far will handle most cases, there may be times you need something more powerful. Here are a couple advanced lookup formulas to consider.

Finding First Match Across Multiple Columns

To find the first occurrence of a value across multiple columns, you can use an array formula like this:

=INDEX(A2:E10,MATCH(TRUE,INDEX((A2:E10=”Value”)+(A2:E10<>””),0),0))

This searches for “Value” in the range A2:E10 and returns the first cell that matches. It’s an array formula, so you need to enter it by pressing Ctrl+Shift+Enter.

Finding Last Occurrence of a Value

To find the last match rather than the first, you can flip the lookup_vector in LOOKUP:

=LOOKUP(2,1/(A2:A10=”Value”),ROW(A2:A10))

This returns the row number of the last match. You can then use that row number in an INDEX formula to get the actual value:

=INDEX(A2:A10,LOOKUP(2,1/(A2:A10=”Value”),ROW(A2:A10)))

Summary

As you can see, Excel provides several ways to look up the first value meeting certain criteria. The LOOKUP function, combined with logical comparisons and the IF and IFERROR functions, allows you to find the first value besides 0 or matching other conditions. These formulas can save you a lot of time when working with large datasets.

Remember, the key steps are:

1. Set up the logical comparison to check each value in the lookup range (e.g., A2:A10<>0).
2. Convert the results to 1s and 0s with a formula like 1/(…).
3. Use LOOKUP to find the position of the first 1 and return the corresponding value.
4. Wrap the formula in IF and IFERROR to handle special cases.

With a solid understanding of these lookup formulas, you’ll be able to quickly extract key information from your Excel data. Experiment with the different variations to find what works best for your specific needs.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.