offsets (I think)
 

I have found a value using the MAX formula and would now like to return the
value found in the cell one row above and one column before the original
position of this result. Is this possible?

offsets (I think)
 

Here's one way...

Assume this is your data:

...........A..........B
1........A.........22
2........B.........17
3........C.........44
4........D.........12
5........E..........15

=INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0)-1)

This will work correctly *unless* the max value in B1:B5 is located in B1.
So, what result do want if that is the case?

--
Biff
Microsoft Excel MVP


"Stan" wrote in message
...
I have found a value using the MAX formula and would now like to return the
value found in the cell one row above and one column before the original
position of this result. Is this possible?

offsets (I think)
 

Biff,
A slight mod gives the opportunity to identify, and act on, the max in B1
situation:
=IF(MATCH(MAX(B1:B5),B1:B5,0)=1,"Max is in 1st
row",INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0)-1))
but I figure you already had that in the back of your head. As you asked,
what to do in that case is still up in the air.

Another oddity of this situation is that "E" (A5) can never be returned.
Hopefully that works in with the OPs needs also.

"T. Valko" wrote:

Here's one way...

Assume this is your data:

...........A..........B
1........A.........22
2........B.........17
3........C.........44
4........D.........12
5........E..........15

=INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0)-1)

This will work correctly *unless* the max value in B1:B5 is located in B1.
So, what result do want if that is the case?

--
Biff
Microsoft Excel MVP


"Stan" wrote in message
...
I have found a value using the MAX formula and would now like to return the
value found in the cell one row above and one column before the original
position of this result. Is this possible?



.

offsets (I think)
 

Thankyou both Joel and t valko for your prompt replies. with a little playing
I was able to make mr valko's suggestion work satisfactorily

offsets (I think)
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Stan" wrote in message
...
Thankyou both Joel and t valko for your prompt replies. with a little
playing
I was able to make mr valko's suggestion work satisfactorily