lookup over a range

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Say I have an array with times and temps:

1:00 75
2:00 77
3:00 78
..
..
..

and so on. I would like to find a function that looks for the first
temp that is over some user-defined temp (76 for example). So in this
case, the result would be 2:00.

Any ideas??
 
Swap the column positions and use VLookup.

= Vlookup(76,A2:B4,2,False)
would return 2:00 if temps were in A and Times were in B

HTH,
 
That works (using TRUE instead of FALSE).

Now here's a curveball:

Say each time and temp is connected to a certain thermometer A, B, and
C like this:

Therm Temp Time
A 76 1:00
A 77 1:30
A 78 1:45
B 79 1:00
B 74 1:15
B 78 1:45
C 78 1:00
C 76 2:00
C 78 3:00

In this case, it should find the time corresponding to the temp
closest to 76 within each thermometer.

So for Thermometer A, it would give 1:00; for Thermometer B, it would
give 1:15; for Thermometer C, it would give 2:00.

Can this be done?
 
That formula *doesn't* work as you originally described in your post.

It returns the time of the temperature *lower* then the target, where you
asked for the *higher* temperature time.

Now, you ask for *closest* temperature.

Does that include higher as well as lower temperatures, as long as they're
the closest to the target.

There's a big difference in formulas to return those different solutions.

What *EXACTLY* are you looking for?
 
Using the closest temp works fine since the temp differences I'm
dealing with are pretty small.
 
Back
Top