A SIMPLER FORMULA TO GET A REFERENCE OF WHICH COLUMN IS POPULATED

[Ravi]

Hi, need help on for a simpler formula is available if available. The
problem is I have a spreadsheet with many columns and I would like to know
which column is populated, for the moment I'm using the following :

=IF(I15>0,COLUMN(I15),"")&IF(J15>0,COLUMN(J15),"")&IF(K15>0,COLUMN(K15),"")&IF(M15>0,COLUMN(M15),"")&IF(N15>0,COLUMN(N15),"")&IF(O15>0,COLUMN(O15),"")

The values can only be positive.
A simpler version would be appreciated.
Thanks

[Aladin Akyurek]

=MATCH(9.99999999999999E+307,I15:IV15)+COLUMN(I15)-1

Ravi wrote:
> Hi, need help on for a simpler formula is available if available. The
> problem is I have a spreadsheet with many columns and I would like to know
> which column is populated, for the moment I'm using the following :
>
> =IF(I15>0,COLUMN(I15),"")&IF(J15>0,COLUMN(J15),"")&IF(K15>0,COLUMN(K15),"")&IF(M15>0,COLUMN(M15),"")&IF(N15>0,COLUMN(N15),"")&IF(O15>0,COLUMN(O15),"")
>
> The values can only be positive.
> A simpler version would be appreciated.
> Thanks

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Max]

> .. know which column is populated ..

If only a single cell within I15:O15 would be populated at any time,
then this might suffice:

=IF(ISNA(MATCH(TRUE,I15:O15<>"",0)),"",MATCH(TRUE,I15:O15<>"",0)+8)

Formula to be array-entered, i.e. press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ravi" <[email protected]> wrote in message
news:[email protected]...
> Hi, need help on for a simpler formula is available if available. The
> problem is I have a spreadsheet with many columns and I would like to know
> which column is populated, for the moment I'm using the following :
>
>
=IF(I15>0,COLUMN(I15),"")&IF(J15>0,COLUMN(J15),"")&IF(K15>0,COLUMN(K15),"")&
IF(M15>0,COLUMN(M15),"")&IF(N15>0,COLUMN(N15),"")&IF(O15>0,COLUMN(O15),"")
>
> The values can only be positive.
> A simpler version would be appreciated.
> Thanks

[Ravi]

Thanks, That's great.

This formula works. Just 1 issue I'm getting which is it also picking up
columns with Zeros' (this is a result of a formula) . I wanted to extract
only the positive values. Is there another tweak to it that I need to make.


"Aladin Akyurek" wrote:

> =MATCH(9.99999999999999E+307,I15:IV15)+COLUMN(I15)-1
>
> Ravi wrote:
> > Hi, need help on for a simpler formula is available if available. The
> > problem is I have a spreadsheet with many columns and I would like to know
> > which column is populated, for the moment I'm using the following :
> >
> > =IF(I15>0,COLUMN(I15),"")&IF(J15>0,COLUMN(J15),"")&IF(K15>0,COLUMN(K15),"")&IF(M15>0,COLUMN(M15),"")&IF(N15>0,COLUMN(N15),"")&IF(O15>0,COLUMN(O15),"")
> >
> > The values can only be positive.
> > A simpler version would be appreciated.
> > Thanks
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.
>

[Max]

Perhaps another play to try in the interim ..

Assuming only a single cell within I15:O15 would evaluate to > 0 at any
time, try, array-entered:

=IF(ISNA(MATCH(TRUE,I15:O15>0,0)),"",MATCH(TRUE,I15:O15>0,0)+8)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Ravi]

Thanks guys. That was very useful and successful.

"Max" wrote:

> Perhaps another play to try in the interim ..
>
> Assuming only a single cell within I15:O15 would evaluate to > 0 at any
> time, try, array-entered:
>
> =IF(ISNA(MATCH(TRUE,I15:O15>0,0)),"",MATCH(TRUE,I15:O15>0,0)+8)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

[Max]

Glad to hear that, Ravi !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ravi" <[email protected]> wrote in message
news:[email protected]...
> Thanks guys. That was very useful and successful.

[Krishnakumar]

Hi Ravi,

Try this array formula,

=MAX(IF(ISNUMBER(I15:O15)*(I15:O15<>0),COLUMN(I15:O15)))

HTH