returning column number

[George Andersen]

Hello All,

Sure is a wealth of information here, I'm hoping someone can help me
out with a function to return the first column letter which contains a
value greater than 0. For Example:

A B C D E RESULT
1 TEST 0 0 3 1 "D"
2 TEST1 0 2 0 2 "C"
3 TEST2 1 6 0 3 "B"
4 TEST3 0 0 0 1 "E"
5 ETC...

Any direction is greatly appreciated,

George

 

[Frank Kabel]

Hi
one way to return the column number:
=MATCH(0,B1:E1,1)+1
and copy down

to get the column letter try
=LEFT(ADDRESS(1,MATCH(0,B1:E1,1)+1,4),1)

 

[Bob Phillips]

Hi George,

Here is one way

=CHAR(MIN(IF(B1:Y1>0,COLUMN(B1:Y1)))+64)

change Y1 to the last column, and commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi change the formulas to
=MATCH(0,B1:E1,1)+2
or
=LEFT(ADDRESS(1,MATCH(0,B1:E1,1)+2,4),1)

 

[Peo Sjoblom]

This formula will return the cell address
of the match in the first row

=CELL("address",INDEX(1:1,MATCH(1,(1:1>0)*(ISNUMBER(1:1)),0)))

entered with ctrl + shift & enter

using your example it will return $D$1

copy down to return the other addresses
if by any chance the numbers are text use

=CELL("address",INDEX(A1:Z1,0,MATCH(1,(1:1>0)*(ISNUMBER(--(1:1))),0)))

 

[George Andersen]

Thank you.

The formula works, however I continuously get a "circular reference"
error.

Any Suggestions?

Thanks,
George

 

[Aladin Akyurek]

In F2 enter & copy down:

=INDEX($B$1:$E$1,MATCH(TRUE,INDEX(B2:E2>0,1,0),0))

 

[George Andersen]

This worked to return the value in the left most cell, but I am trying
to get the column letter / number of the leftmost cell that has a
number greater than 0.

 

[Frank Kabel]

Hi
try entering the formula as array formula (CTRL+SHIFT+ENTER)

 

[Peo Sjoblom]

If your formula is in the same row you would get an error, however just
change the
references to something like A1:H1 or whatever the size of your data is

 

[Bob Phillips]

Frank,

This fails if no 0's.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Aladin Akyurek]

Given that A1:E5 houses...

{"A","B","C","D","E";"TEST",0,0,3,1;"TEST1",0,2,0,2;"TEST2",1,6,0,3;"TEST3",
0,0,0,1}

the result area shows:

{"RESULT";"D";"C";"B";"E"}

Precisely the results you posted along with your data.

Maybe you meant:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX(B2:E2,MATCH(TRUE,INDEX(B2:E2>0,1,0),0))),
4),"1","")

to be entered in F2.

 

[Aladin Akyurek]

No, Frank. The formula as set up does not require confirming with
control+shift+enter.

 

[Frank Kabel]

Hi Bob
correct.
For the OP: Though one can add some error checking to this formula I'd
recommend to use Peo's or Bob's solutions as they do not require this
kind of error checking

 

[Frank Kabel]

Hi Aladin
have to read formulas more carefully. You're of course right (nice
trick by the way)
Frank

 

[Harlan Grove]

In F2 enter & copy down:

=INDEX($B$1:$E$1,MATCH(TRUE,INDEX(B2:E2>0,1,0),0))

...

Clever way to avoid array entry, but the INDEX call is unnecessary. Entering

=INDEX($B$1:$E$1,MATCH(TRUE,B2:E2>0,0))

as an array formula would produce the same result, and it should recalc faster.

 

[George Andersen]

That did the trick. Thank you.
-George

Given that A1:E5 houses...

{"A","B","C","D","E";"TEST",0,0,3,1;"TEST1",0,2,0,2;"TEST2",1,6,0,3;"TEST3",
0,0,0,1}

the result area shows:

{"RESULT";"D";"C";"B";"E"}

Precisely the results you posted along with your data.

Maybe you meant:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX(B2:E2,MATCH(TRUE,INDEX(B2:E2>0,1,0),0))),
4),"1","")

to be entered in F2.