# Find max value in a range and display adjacent values appearing in same row

1. ## Find max value in a range and display adjacent values appearing in same row

Hi

I'm looking for a formula that will find the max value (in col C) in a range, and then the value that is 5 rows below the max value, and then place both of these values in separate columns (H and I). There are many subjects (~100) and these are coded numerically in col A (1,2,3 etc), and so I need the formula to apply this range for each subject before moving on to the next one.

The next stage is to also display the data from col D and E which appear in the same rows as the max and max + 5 values of Col C (in col J and K for value 2, L and M for value 3 etc. Note this are not necesarily the max values in their own column, rather they are just the corresponding values that occur in the same row as the max value in col C.

There may be problems arising if the max value occurs with less then 5 rows remaining in the range. This will not occur often as the max should usually be towards the top of the range, but in some cases where it might, is it possible for the formula to return the last value in the range for the subject and not the value for the next subject (which may technically be the 5th row after the max)

I have attached a sample worksheet. Please tell me if you require any additional information

I understand that this is a reasonably complex problem and so I thank you in advance for your assistance

T85K

2. ## Re: Find max value in a range and display adjacent values appearing in same row

Hi,

These are all array formulas and so need to be confirmed with CTRL+SHIFT+ENTER, not just ENTER:

In H2 and copy down:

=MAX(IF(\$A\$2:\$A\$61=G2,\$C\$2:\$C\$61))

In I2 and copy down:

=INDEX(\$C\$2:\$C\$61,MIN(MATCH(G2&H2,\$A\$2:\$A\$61&\$C\$2:\$C\$61,0)+5,MAX(IF(\$A\$2:\$A\$61=G2,ROW(\$A\$2:\$A\$61)-1))))

In J2 and copy down:

=INDEX(\$D\$2:\$D\$61,MATCH(G2&H2,\$A\$2:\$A\$61&\$C\$2:\$C\$61,0))

In K2 and copy down:

=INDEX(\$D\$2:\$D\$61,MATCH(G2&I2,\$A\$2:\$A\$61&\$C\$2:\$C\$61,0))

In L2 and copy down:

=INDEX(\$E\$2:\$E\$61,MATCH(G2&H2,\$A\$2:\$A\$61&\$C\$2:\$C\$61,0))

In M2 and copy down:

=INDEX(\$E\$2:\$E\$61,MATCH(G2&I2,\$A\$2:\$A\$61&\$C\$2:\$C\$61,0))

You will notice that these will agree with all of your expected values except for the Value 2 + 5 and Value 3 + 5 figures for Subject=3 since your highlighted Max value of 59 in row 61 for this Subject is equally present in row 42 and you do not appear to have offered a resolution to this ambiguity in your description.

Regards

3. ## Re: Find max value in a range and display adjacent values appearing in same row

no need for any arrays
h2=
Formula:
`Please Login or Register  to view this content.`

i2
Formula:
`Please Login or Register  to view this content.`

4. ## Re: Find max value in a range and display adjacent values appearing in same row

These are both excellent solutions, thank you very much!

There are currently 1 users browsing this thread. (0 members and 1 guests)