Looking up a column value versus another column value
 

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!

Assuming your data starts in cell A1...

=OFFSET(A1, MATCH(MAX(B2:B5), B2:B5), 0)
--
HTH...

Jim Thomlinson


"Jason" wrote:

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!

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






"Jim Thomlinson" wrote:

Assuming your data starts in cell A1...

=OFFSET(A1, MATCH(MAX(B2:B5), B2:B5), 0)
--
HTH...

Jim Thomlinson


"Jason" wrote:

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!

See my post in:

http://tinyurl.com/562xz

Jason wrote:
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!

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).
--
HTH...

Jim Thomlinson


"Jason" wrote:

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






"Jim Thomlinson" wrote:

Assuming your data starts in cell A1...

=OFFSET(A1, MATCH(MAX(B2:B5), B2:B5), 0)
--
HTH...

Jim Thomlinson


"Jason" wrote:

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!

Aladin, is there a way to simplify the equation? I am not concerned about ties.

"Aladin Akyurek" wrote:

See my post in:

http://tinyurl.com/562xz

Jason wrote:
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!

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

=INDEX(TimeRange,MATCH(MAX(ResultRange),ResultRang e,0))

Jason wrote:
Aladin, is there a way to simplify the equation? I am not concerned about ties.

"Aladin Akyurek" wrote:


See my post in:

http://tinyurl.com/562xz

Jason wrote:

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!


--

[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.