Can I do this?

S

Steven Sinclair

If I have two rows of numbers...

5 10 15 20 25 30 35 40 45 50
1 16 23 18 64 28 46 72 18 26

Is there a formula I can insert, in another cell elsewhere on the sheet,
that will find the largest value in the second row, but return the
corresponding number from the first row? In the above example, I could use
"=max(b1:b10)" to find the largest number in row b, but how do I reference
row a?

Thanx.
 
B

Bob Phillips

=INDEX(1:Try,,MAX(2))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Sorry, that response got messed.

Try this formula

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

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

One way:

=INDEX(A1:J1,MATCH(MAX(A2:J2),A2:J2))

(did you really mean rows of numbers or columns of numbers (=max(b1:b10))????
 
B

bpeltzer

You've started down the right path... MAX to get the max value in row 2,
then MATCH to find how far into the row that value is found, then INDEX to
get the corresponding entry from row 1. Something like
=index(A1:J1,match(max(A2:J2),A2:J2,false)).
 
S

Steven Sinclair

Cool...gettin' closer!

8^)>

Now, how can I accomplish the same feat, but instead of searching for the
largest number and giving me the corresponding number, search for the five
highest and give me the five corresponding numbers.

Thanx again.
 
B

Bob Phillips

=INDEX(1:1,,MATCH(LARGE(2:2,1),2:2,0))
=INDEX(1:1,,MATCH(LARGE(2:2,2),2:2,0))
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top