Find max value in range and return corresponding value in offset range

[burnsy180]

Hi all,

very frustrating simple problem.

I have a table from A5:G10 and another table N5:T10

I need to find the MAX value in table A5:10, which is fine.

But i then need to find the corresponding value in the table N5:T10.

So basically, i'm finding the position of MAX(A5:G10), then finding the value in the same row but offset 13 columns to the right.

It seems so simple and i've tried so many different formulas, but i keep getting #N/A

I tried =OFFSET(A5,MATCH(MAX(A5:G10),A5:G10,0),13) and i just get #N/A. SO FRUSTRATING!

I thought this should work but i have no idea why it isn't.

Can anyone help with any ideas? thanks

Joel

p.s: I'm running Excel 2003

[zbor]

Try

=INDEX(N5:T10, MATCH(MAX(A5:G10),A5:G10,0))

[burnsy180]

Try

=INDEX(N5:T10, MATCH(MAX(A5:G10),A5:G10,0))

Nope I got the exact same #N/A again...grrr

[zbor]

Do you have #N/A in A5:G10 range?

Please upload example workbook.

[burnsy180]

Do you have #N/A in A5:G10 range?

Please upload example workbook.

No, the lowest value is 0, highest is 1

Mapsmacros.xlsx

[zbor]

Oh, yes, sorry.. my fault.. you can't match in multiple rows that way... let me see...

[zbor]

Can you add one helper column:

[tom1977]

Hi thats why I do not like formulas with this simple code you can obtain the same

Please Login or Register  to view this content.

[burnsy180]

Ahh ok. So you can't find the coordinates of the max in that range and just offset a few columns?

Seems a bit odd.
But yeah what you've done there seems to work

No other way to do it without a helper column?

[zbor]

as tom said, with VBA code.
But you can hide that helper column so there shoudln't be a problem...