Lookup temperature based on closest time using INDEX and MATCH

Hi All,

I'm looking to find the ambient temperature based on the time in one column to the closest time of another column to bring back the temperature from that closest time.

I tried to use VLOOKUP and the INDEX & MATCH function with no luck.

=INDEX('09_02_Charts'!D2:D253,MATCH('09_02_Charts'!A2,Historical_Weather_Data!C2:C253,1))

Thanks for the help!

Thanks so much Speshul!

That worked like a champ! Can't believe I've spent the better part of 4hrs trying to figure this out. So I understand the above correctly, the "ABS" simply puts the date and time with an absolute value to compare against. and then the "C:D" in the lookup table goes throughout the end of the data so when I add additional data, I no longer have to change this value. Is that correct?

Makes complete sense.

Thanks you very much. I've learned something new today and now with this forum in hand I can become an expert over time.

Originally Posted by gshafiq

Hi All,

I'm looking to find the ambient temperature based on the time in one column to the closest time of another column to bring back the temperature from that closest time.

I tried to use VLOOKUP and the INDEX & MATCH function with no luck.

=INDEX('09_02_Charts'!D2:D253,MATCH('09_02_Charts'!A2,Historical_Weather_Data!C2:C253,1))

Thanks for the help!

Originally Posted by Speshul

Change the formula on the Historical_weather_data tab, Column C

=ABS(TEXT(A2,"mm/dd/yy ") & TEXT(B2,"hh:mm:ss AM/PM"))

This is correct

Originally Posted by Speshul

And use this VLOOKUP

=VLOOKUP(A2,Historical_Weather_Data!C:D,2,1)

This is almost correct. VLOOKUP returns the closest value less than or equal to the lookup value, same with MATCH having a match type of 1, but sometimes the closest value is the next time after the VLOOKUP result,e.g. row 6 on the '09_02_Charts' tab has a date/time of '8/29/2014 6:31:00 PM', for which VLOOKUP returns the temp from row 23 of the 'Historical_Weather_Data' tab (8/29/14 5:53 PM, 38 minutes before) but row 24 (8/29/14 6:53 PM, 22 minutes after) is in fact the closest time. This requires a bit more calculation to return the correct value.

(long formula here...)

Please Login or Register  to view this content.

Basically compare the target date/time to the less than or equal date/time returned by index/match and then compare target to the index/match+1 (to check the next row after) and then display the results. Excel file attached, with data highlighted to show results.