Find max in one row and extract value from another

[JohannesFust]

Hello,

I want to find the maximum in row 2 and extract the corresponding number in row 1.

Ex:
row 1: 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1
row 2: 1/1-12, 1/2-12, 1/3-12, N/A, 1/5-12, 1/6-12, E: 1/7-12, E: 1/8-12, N/A, E: 1/1-12

In this case max(A2:J2) is 1/6/12, which is correct. So I want the number 0.7 and have tried a couple of different formulas without luck.

your help would be highly appreciated.

[NickyC]

Hi
what is the format of the numbers on row 2 - are they dates? if so, is 2/3-12 2 March 2012 or 3 February 2012?

[JohannesFust]

They are dates. The actual format is 2-Mar-12 or 02-03-12 and so on.

[NickyC]

This custom function should do it - it assumes that the row to return a number from is immediately above the row with your date values. If not, you may have to adjust the offset part of the code.

Please Login or Register  to view this content.

[JohannesFust]

Thanks. But what I need is a formula in Excel 2007. Ive tried the address function to give a certain row and depend the column of max(range of dates), but this does not work.

[NickyC]

or, assuming again that your dates start in cell A2 and your reference values are in the row above, this works without a user-defined function:

=OFFSET(A2,-1,MATCH(MAX(A2:Z2),A2:Z2)-1)

[JohannesFust]

It works, Im not sure how but it does. Thank you very much.