Formula to find MAX value and return value from 1 column to the right.

[saundej]

Hi,

I have quite a large spreadsheet in which I need to find the max value from a list of cells (i.e. AX4,AT4,AP4,AL4,AH4,AD4,Z4,V4,R4,N4), which I can do using MAX, however I need to return the value from the cell 1 column to the right rather than the actual MAX value. So if the max value is in cel AX4, then I'd need to return the value from AY4

I've tried various formulas I've found in forums but cant quite get them to work, does anyone have any ideas?

[Down Under]

Without seeing what your complete range is it's hard to tell what will work, but from what you have described I think this should work:

=index(n1:ay4,4,match(max(n4:ax4),n4:ax4,0)+1)

However, that will only work if the cells you haven't specified that fall within the range n4:ax4 are not numbers. If they are, then you will have to replace max(n4:ax4) with max (ax4+at4+ap4 etc.)