OFFSET function with variable reference cell

**smile2leksa** · 05-06-2009, 04:56 AM

Hello!
I search a number with maximum value in column "B", let's say, this number is siuated in cell "B5".
Now I have to retrieve the contents of a cell situating left from "B5": contents of cell "A5". If maximum value would always stand at cell "B5" I could easily use function OFFSET.
But if I change data in column "B", maximum value's address will change. How can I retrieve the contents of cell situating in the left column from the dynamic cell "BX" where X is a row number that may change?
Thanks!

**DonkeyOte** · 05-06-2009, 05:00 AM

Perhaps you mean

=INDEX(A:A,MATCH(MAX(B:B),B:B,0))

Incidentally try to avoid using OFFSET as it is Volatile - normally an INDEX approach can be used with equal effect.