Lookup Array Formula

A

aldsv

A dozen google searches didn't solve this problem, but I know yo
can...

I have a row of data (I actually have a lot of rows, and a lot o
columns):

[0,0,0,5,2,0,3]

I need a formula to find the column# of the first cell with <>0 data.

I tried various combinations of match, min, max and array formulas t
no success

Thanks in advance.

alds
 
B

Bruno Campanini

aldsv said:
A dozen google searches didn't solve this problem, but I know you
can...

I have a row of data (I actually have a lot of rows, and a lot of
columns):

[0,0,0,5,2,0,3]

I need a formula to find the column# of the first cell with <>0 data.
Click to expand...

Given $K$71:$Q$71 the row containing your data, the following
returns 14 (column N containing 5):

{=MIN(IF(IF($K$71:$Q$71<>0,COLUMN($K$71:$Q$71),0)>0,
IF($K$71:$Q$71<>0,COLUMN($K$71:$Q$71),0)))}
FormulaArray

Ciao
Bruno
 
B

Bruno Campanini

Better:
Given $K$71:$Q$71 the row containing your data, the following
returns 14 (column N containing 5):

{=MIN(IF($K$71:$Q$71<>0,COLUMN($K$71:$Q$71),257))}
FormulaArray

Ciao
Bruno
 
R

Ron Rosenfeld

A dozen google searches didn't solve this problem, but I know you
can...

I have a row of data (I actually have a lot of rows, and a lot of
columns):

[0,0,0,5,2,0,3]

I need a formula to find the column# of the first cell with <>0 data.

I tried various combinations of match, min, max and array formulas to
no success

Thanks in advance.

aldsv
Click to expand...

This **array** formula should do what you require:

=MATCH(TRUE,Range<>0,0)+COLUMN(Range)-1

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

It also excludes <blank>'s from the test, so that

0,0,,5,2,0,3 is equivalent to 0,0,0,5,2,0,3

If this is not what you want, please clarify.


--ron
 
Top