Looking up a column value versus another column value

J

Jason

Time Result
0 0
5 300
10 52000
30 100

So that when the "result" is at a max, the coressponding time is retrived.

Thanks!
 
J

Jim Thomlinson

Assuming your data starts in cell A1...

=OFFSET(A1, MATCH(MAX(B2:B5), B2:B5), 0)
 
J

Jason

Thanks Jim! Almost there for some reason it doesn't work my actual data set.
Any ideas?

Time Value
0 0
5 28600
15 36000
30 28500
60 24000
120 5000
180 1800
240 425
360 50
 
J

Jim Thomlinson

Then it does work with the small test data set you gave me? If that is the
case then confirm the the range of cells is defined correctly in the formula
and that all of the numbers in those cells are actually numbers and not text
(Try increasing the decimal places. Numbers have decimal places text does
not).
 
A

Aladin Akyurek

I'd think that you should be concerned. If not:

=INDEX(TimeRange,MATCH(MAX(ResultRange),ResultRange,0))
Aladin, is there a way to simplify the equation? I am not concerned about ties.

:
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
 
Top