http://www.excelforum.com/excel-formulas-and-functions/679226-find-the-first-value-in-a-row-greater-than-zero.html

- 04-09-2009, 01:35 AM#1

- Join Date
- 11-16-2008
- Location
- Adelaide,Australia
- Posts
- 12

## Find the first value in a row greater than zero

I'm trying to find the column number of the first cell in a row that is greater than zero.

There are 31 columns of of data, so if statments aren't working for me.

any suggestions

Example

1 2 3 4 5 6 7 8 9

A 0 0 0 0 0 0 8 1 0 = 7

B 0 0 2 0 0 0 0 0 0 = 3

C 0 0 0 0 0 0 0 0 1 = 9

Thanks - 04-09-2009, 03:18 AM#2
## Re: Find the first value in a row greater than zero

AF1: =MATCH(TRUE,$A1:$AE1>0,0)

**confirmed with CTRL + SHIFT + ENTER**

copy down as required

EDIT: I spotted from a daddlylonglegs post a way to use INDEX within the MATCH and thereby avoid need for Array...

**AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)**Last edited by DonkeyOte; 04-09-2009 at 03:35 AM. Reason: typo & dll edit

My Recommended Reading:

Volatility

Sumproduct & Arrays

Pivot Intro

Email from XL - VBA & Outlook VBA

Function Dictionary & Function Translations

Dynamic Named Ranges - 06-14-2012, 12:42 PM#3

- Join Date
- 06-13-2012
- Location
- Hamsterdam
- MS-Off Ver
- Excel 2010
- Posts
- 17

- 08-02-2012, 11:54 AM#4

- Join Date
- 06-15-2012
- Location
- Sacramento, CA
- MS-Off Ver
- Excel 2010
- Posts
- 7

## Re: Find the first value in a row greater than zero

Fantastic! That is very useful!

- 08-06-2013, 01:29 PM#5

- Join Date
- 08-06-2013
- Location
- poznaÅ„
- MS-Off Ver
- Excel 2010
- Posts
- 1

## Re: Find the first value in a row greater than zero

Thanks a lot for saving my ***!

http://office.microsoft.com/en-us/excel-help/match-function-HP010062414.aspx

# MATCH function

This article describes the formula syntax and usage of the MATCH function in Microsoft Office Excel.## Description

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula=MATCH(25,A1:A3,0)returns the number 2, because 25 is the second item in the range.Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the*row_num*argument of the INDEX function.## Syntax

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments:- lookup_value Required. The value that you want to match in
*lookup_array*. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

The*lookup_value*argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.- lookup_array Required. The range of cells being searched.
- match_type Optional. The number -1, 0, or 1. The
*match_type*argument specifies how Excel matches*lookup_value*with values in*lookup_array*. The default value for this argument is 1.

The following table describes how the function finds values based on the setting of the*match_type*argument.MATCH_TYPE BEHAVIOR 1 or omitted MATCH finds the largest value that is less than or equal to *lookup_value*. The values in the*lookup_array*argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.0 MATCH finds the first value that is exactly equal to *lookup_value*. The values in the*lookup_array*argument can be in any order.-1 MATCH finds the smallest value that is greater than or equal to *lookup_value*. The values in the*lookup_array*argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.NOTES- MATCH returns the position of the matched value within
*lookup_array*, not the value itself. For example,MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}. - MATCH does not distinguish between uppercase and lowercase letters when matching text values.
- If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
- If
*match_type*is 0 and*lookup_value*is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the*lookup_value*argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

## Example

The example may be easier to understand if you copy it to a blank worksheet.1 2 3 4 5 6

7

8

9A B C Product Count Bananas 25 Oranges 38 Apples 40 Pears 41 Formula Description Result =MATCH(39,B2:B5,1) Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned. 2 =MATCH(41,B2:B5,0) The position of the value 41 in the range B2:B5. 4 =MATCH(40,B2:B5,-1) Returns an error because the values in the range B2:B5 are not in descending order. #N/A Did this article help you?

## No comments:

Post a Comment