Matching in arrays (with a directional specific component)

[Rick_B]

I'm looking for a function that will find a maximum value in column C
and then report the correlating numbers in columns A & B of the sam
row (that of the max value).

After returning those three values, I need to then find a value exactl
15 rows above the max value cell (from Column C) and the correlatin
values in A & B

And lastly, I need to then find a value exactly 15 rows below the ma
value cell (from Column C) and its correlating values in A & B.

Columns A & B are in ascending order, but column C is not. Each colum
contains 2000 rows of data. Fun Stuff.

I'd greatly appreciate anyone that has any clues as to how I could d
this.
--Ric

Attachment filename: matching question.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=43668

 

[Frank Kabel]

Hi

I'm looking for a function that will find a maximum value in column C,
and then report the correlating numbers in columns A & B of the same
row (that of the max value).

Try
=MAX(C1:C2000)
for returning the max value of column C

=INDEX(A1:A2000,MATCH(MAX(C1:C2000),C1:C2000,0),1)
for returning the corresponding value in column A

=INDEX(B1:B2000,MATCH(MAX(C1:C2000),C1:C2000,0),1)
for returning the corresponding value in column B

After returning those three values, I need to then find a value
exactly 15 rows above the max value cell (from Column C) and the
correlating values in A & B

For the value in column C
=INDEX(C1:C2000,MATCH(MAX(C1:C2000),C1:C2000,0)-15,1)
(note this will return an error, if there are no 15 lines above the max
value)
apply this formula for A & B

And lastly, I need to then find a value exactly 15 rows below the max
value cell (from Column C) and its correlating values in A & B.

This should be easy now

Frank