Finding a maximum with VLOOKUP
 

I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.

Finding a maximum with VLOOKUP
 

Try this:

=INDEX(T2_range,MATCH(MAX(Delta_range),Delta_range ,0))

--
Biff
Microsoft Excel MVP


"Russ" wrote in message
ps.com...
I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.

Finding a maximum with VLOOKUP
 

Ooops!

return the time value 2

I interpreted that as the T2 value.

Just need to change the column reference:

=INDEX(Time_range,MATCH(MAX(Delta_range),Delta_ran ge,0))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=INDEX(T2_range,MATCH(MAX(Delta_range),Delta_range ,0))

--
Biff
Microsoft Excel MVP


"Russ" wrote in message
ps.com...
I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.

Finding a maximum with VLOOKUP
 

On Aug 16, 2:33 pm, "T. Valko" wrote:
Ooops!

return the time value 2

I interpreted that as the T2 value.

Just need to change the column reference:

=INDEX(Time_range,MATCH(MAX(Delta_range),Delta_ran ge,0))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message

...

Try this:

=INDEX(T2_range,MATCH(MAX(Delta_range),Delta_range ,0))

--
Biff
Microsoft Excel MVP

"Russ" wrote in message
ups.com...
I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.

Thank you! This is exactly what I was looking for.

Russ D.

Finding a maximum with VLOOKUP
 

"Russ" wrote in message
oups.com...
On Aug 16, 2:33 pm, "T. Valko" wrote:
Ooops!

return the time value 2

I interpreted that as the T2 value.

Just need to change the column reference:

=INDEX(Time_range,MATCH(MAX(Delta_range),Delta_ran ge,0))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message

...

Try this:

=INDEX(T2_range,MATCH(MAX(Delta_range),Delta_range ,0))

--
Biff
Microsoft Excel MVP

"Russ" wrote in message
ups.com...
I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.

Thank you! This is exactly what I was looking for.

Russ D.


You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Finding a maximum with VLOOKUP
 

The LOOKUP function requires the lookup_vector be sorted in ascending order.

Even though it works on the posted sample, try replacing the Delta 25 with
210 and see what happens.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Try,

=LOOKUP(MAX(D2:D5),D2:D5,A2:A5)

Assumes your dat are in columns A,B,C & D

Mike

"Russ" wrote:

I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.

Finding a maximum with VLOOKUP
 

Thanks for that I forgot. I think I've misunderstood the Op's question anyway.

Mike

"Biff" wrote:

The LOOKUP function requires the lookup_vector be sorted in ascending order.

Even though it works on the posted sample, try replacing the Delta 25 with
210 and see what happens.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Try,

=LOOKUP(MAX(D2:D5),D2:D5,A2:A5)

Assumes your dat are in columns A,B,C & D

Mike

"Russ" wrote:

I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.

Finding a maximum with VLOOKUP
 

I think index match would work better than vlookup

=index(Time-range,match(max(Delta_range),Delta_range,0))

"Russ" wrote:

I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.

Finding a maximum with VLOOKUP
 

Try,

=LOOKUP(MAX(D2:D5),D2:D5,A2:A5)

Assumes your dat are in columns A,B,C & D

Mike

"Russ" wrote:

I have a set of data time/temperature data with two temperature values
specified at each time point. I am interested in finding the time
point where the difference between this two temperatures reaches a
maximum. For example:

time T1 T2 Delta
0 175 200 25
1 150 200 50
2 100 300 200
3 175 225 50

'time', 'T1', and 'T2' are numerical values while 'Delta' is a formula
calculating the difference between 'T1' and 'T2'.

I would like to develop a lookup function to identify the value of 200
as a maximum and return the time value 2 where this maximum is
reached.

I've tried to use the MAX() as a lookup value in a VLOOKUP() function
but it doesn't seem to work.

Any ideas?

Thanks,
Russ D.