Search code examples
excelfunctionexcel-formulaconditional-statementsoffset

Returning a Value Based On Largest Value in Column Beside It

I am attempting to return a value from Column B that is adjacent with the largest value in Column A. I have my code here, and I can't understand why it isn't working. Any help would be appreciated (and if possible an explanation of where I went wrong and why), thank you.

=OFFSET(ADDRESS(MATCH(LARGE(A:A,1),A:A),1),0,1)
Solution

  • Try,

    =index(b:b, match(max(a:a), a:a, 0))

    Your original is returning an error because (among other problems) address returns a string that looks like a cell address, not a usable cell address. You would need an indirect() wrapper to convert the string to a usable cell address. Maybe something closer to one of these,

    =INDIRECT(ADDRESS(MATCH(LARGE(A:A, 1), A:A, 0), 2))
=OFFSET(INDIRECT(ADDRESS(MATCH(LARGE(A:A,1), A:A, 0), 1)), 0, 1)