How get Excel 2007 to return highest column

[virtualjustin]

I have an array of data. I need a formula that will return the column number
of the highest column with content.


For example:
A B C D E F
1 3 2 3
2 3 4
3 2 4 5 1
4 2 3

Where the answers should be D, E, F, and B, respectively.

Any suggestions?
Thanks
Justin

 

[Teethless mama]

=LOOKUP(10^10,1:1)

 

[T. Valko]

Where the answers should be D, E, F, and B, respectively.

Huh?

I think a better explanation is needed (at least, for me!).

 

[Max]

Assume source data as posted is in Sheet1

In Sheet2,
Put this in any starting cell, say in B2, then array-enter the formula by
pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER
=IF(COUNT(Sheet1!1:1),MATCH(MAX(ISNUMBER(Sheet1!1:1)*COLUMN(1:1)),(ISNUMBER(Sheet1!1:1))*(COLUMN(1:1)),0),"")
B2 returns the required col number of row1 in Sheet1, ie: 4. Copy B2 down to
return correspondingly for the other data rows in Sheet1. You'd get the
stated results: 4,5,6,2 in B2:B5

 

[Ron Rosenfeld]

I have an array of data. I need a formula that will return the column number
of the highest column with content.


For example:
A B C D E F
1 3 2 3
2 3 4
3 2 4 5 1
4 2 3

Where the answers should be D, E, F, and B, respectively.

Hmmm. The last I checked, "D" was not a number <g>.

If you want the column NUMBER, then:


=LOOKUP(2,1/(LEN(1:1)>0),ROW(INDIRECT("1:256")))
for Row 1

If you copy/fill down, the row parameter will adjust for subsequent rows.

and so forth.

If you want the column letter, then try:

=LEFT(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)>0),ROW(INDIRECT("1:256"))),4),
LEN(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)>0),ROW(INDIRECT("1:256"))),4))-1)

And if you want the contents of that cell, you can try:

=LOOKUP(2,1/(LEN(1:1)>0),1:1)


--ron

 

[Harlan Grove]

If you want the column NUMBER, then:

=LOOKUP(2,1/(LEN(1:1)>0),ROW(INDIRECT("1:256")))
for Row 1

....

OP's subject line says Excel 2007, and Excel 2007 has more than 256
columns. Also an unnecessary use of the volatile INDIRECT. Better to
rewrite this as

=LOOKUP(2,1/(LEN(1:1)>0),COLUMN(1:1))

or

If you want the column letter, then try:

=LEFT(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)>0),ROW(INDIRECT("1:256"))),4),
LEN(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)>0),ROW(INDIRECT("1:256"))),4))-1)

....

Or

=SUBSTITUTE(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)>0),COLUMN(1:1)),4),1,"")

 

[Ron Rosenfeld]

...

OP's subject line says Excel 2007, and Excel 2007 has more than 256
columns. Also an unnecessary use of the volatile INDIRECT. Better to
rewrite this as

=LOOKUP(2,1/(LEN(1:1)>0),COLUMN(1:1))

or


...

Or

=SUBSTITUTE(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)>0),COLUMN(1:1)),4),1,"")

Good suggestions.

I never realized, nor tested to see, that COLUMN (and ROW also, for that
matter) would return an array.

So far as 2007, I thought about that when writing my more complex formula to
parse out the column letter, but not when generating the column number itself.
One of these days I'll have to get 2007.
--ron