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

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:

A | B |
---|---|

0 | Red |

0 | Blue |

10 | Green |

25 | Yellow |

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:

- (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}.
- 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.
- 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:

- Set up the logical comparison to check each value in the lookup range (e.g., A2:A10<>0).
- Convert the results to 1s and 0s with a formula like 1/(…).
- Use LOOKUP to find the position of the first 1 and return the corresponding value.
- 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

### What is the basic syntax for the LOOKUP function in Excel?

The basic syntax for the LOOKUP function is: `=LOOKUP(lookup_value, lookup_vector, [result_vector])`

. The `lookup_value`

is the value to search for, the `lookup_vector`

is the one-dimensional range to examine for the lookup_value, and the optional `result_vector`

is the one-dimensional range to return a result from.

### How do I find the first non-zero value in a range using Excel?

To find the first non-zero value in a range (e.g., A2:A10), use the formula: `=LOOKUP(2,1/(A2:A10<>0),A2:A10)`

. This searches the range for the first cell not equal to 0 and returns its value.

### What should I do if all values in the range are 0?

If all values in the range are 0, wrap the formula 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))`

. This will return “No non-zero values” if all cells are 0.

### How can I fix a #DIV/0! error in the formula?

If you encounter a #DIV/0! error, 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)`

. This will return the value in A2 if the formula would throw a #DIV/0! error.

### Can I use this formula to find the first value meeting other criteria besides being non-zero?

Yes, you can adapt the basic formula to find the first value meeting other criteria. For example, to find the first value greater than X, use: `=LOOKUP(2,1/(A2:A10>X),A2:A10)`

. Replace X with the value you want to compare against.

### How do I find the last occurrence of a value in a range?

To find the last occurrence of a value in a range, 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)))`

.

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.