I Need Help Returning Column Numbers

  • Thread starter Thread starter Cory from Eugene
  • Start date Start date
C

Cory from Eugene

I want to be able to search a row of number, find the largest value (LARGE
function), and then return the Column Number of the cell. I cant quite figure
it out.

Can anyone help?

Thanks,
Cory
 
Try something like this:

=MATCH(LARGE(A1:Z1,1),A1:Z1,0)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Assuming Row 1....

=MATCH(MAX(1:1),1:1,0)

If it were Row 3...

=MATCH(MAX(3:3),3:3,0)

Rick
 
Do you want the actual column number or the relative column number?

For example:

......J.....K.....L.....M.....N.....
......8.....2.....7......6......1......

Column J has the highest value. Column J's actual column number is 10 but
its relative column number is 1.
 
i need actual column number

Thanks,
Cory

T. Valko said:
Do you want the actual column number or the relative column number?

For example:

......J.....K.....L.....M.....N.....
......8.....2.....7......6......1......

Column J has the highest value. Column J's actual column number is 10 but
its relative column number is 1.
Click to expand...
 
Try something like this:

=INDEX(COLUMN(J1:N1),MATCH(MAX(J1:N1),J1:N1,0))

If there are duplicate max values the formula will "find" the first instance
only.
 
Can you explain why you can't use it? It (as well as the formula I posted)
both seem to work fine for me.

Rick
 
im going to need to find the second, third, fourth highest and so on. And I
need to be able to find highest numbers even if they are the same
 
it only works for the first highest number. I need to be able to use the same
formula for the second highest, third highest... and so on. And each time, I
need to recall the column number for each highest.

thanks,
cory
 
How are you going to want the results presented to you? Can you use a macro?
I think it may have to be a macro in the end because if there are multiple
LARGE values, the get reported as the 1st, 2nd, etc.; that is, if 16 is the
largest value and there are two them, LARGE(range,1) and LARGE(range,2) are
both 16 (so macro may be needed to identify each 16 as the largest).

Rick
 
Ok, how about telling us the specific location of the data and where you
want the formula(s). It would even help if you could post a small example
with the results you expect.
 
You can make each value unique:

.....10...11...12....13....14.....
......J.....K.....L.....M.....N.....
......8.....2.....7......8......1......

Array entered in J3 and copied across:

=INDEX(COLUMN($J1:$N1),MATCH(LARGE($J1:$N1-COLUMN($J1:$N1)/10^10,COLUMNS($J3:J3)),$J1:$N1-COLUMN($J1:$N1)/10^10,0))

Results: 10, 13, 12, 11, 14
 
Lets say in A1 thru K1 we have:

1 2 10 11 9 7 5 3 8 4 6


In another cell enter:
=MATCH(LARGE($A$1:$K$1,ROWS($A$1:A1)),$A$1:$K$1,0)
and copy down to display:

4
3
5
9
6
11
7
10
8
2
1
 
Back
Top