Match data by different Timestamps from different files in EXCEL-CodePudding

I have an XLS that contains the following rows and columns.

On the left (Columns A-C), I have the Timestamps and the Altitude measured with one device and on the right (Columns G-L), I have the Timestamps and the CO2 measured with a different device. I couldn't manage to make them record the same timestamp, so there are some differences. However, I managed to match them a bit using INDEX and MATCH functions in EXCEL (Column E) to get an approximated CO2 correlated to the Altitude, which does a pretty good job, but it only matches the timestamps that are exact (see the bold text) and I was wondering if it is possible to have better data with a better approximation.

The function used in Column E is:

=INDEX($L$2:$L$200,MATCH(TEXT(A2,"MM/DD/YYYY hh:mm:s")&"*",TEXT($G$2:$G$200,"MM/DD/YYYY hh:mm:s")&"*",0))

I thought that using a hh:mm:s (not ss) format would take only the first digit from :ss, which would have made sense, but it doesn't work. Moreover, on the 5th row on Column E, I get 994, which doesn't match the timeline. Any help would be greatly appreciated.

Can anyone help me find a better function or a better way to write this one so that I get a better matching (say, /-5 seconds error)? Thank you

Here is an importable comma-separated value (no spaces) format of a part of the table:

Time,TimeStamp,Altitude (m),,CO2,,Time,Date,Time,Timestamp,,CO2
12/30/2022 15:00,44925.62562,57.76052475,,977,,12/30/2022 15:00,12/30/2022,15:00:04,1672412404,44925.62505,954
12/30/2022 15:00,44925.62566,57.53207017,,#N/A,,12/30/2022 15:00,12/30/2022,15:00:14,1672412414,44925.62516,955
12/30/2022 15:00,44925.62568,56.58901598,,#N/A,,12/30/2022 15:00,12/30/2022,15:00:24,1672412424,44925.62528,958
12/30/2022 15:01,44925.62573,55.58291246,,994,,12/30/2022 15:00,12/30/2022,15:00:34,1672412434,44925.62539,964
12/30/2022 15:01,44925.62574,55.42074207,,983,,12/30/2022 15:00,12/30/2022,15:00:44,1672412444,44925.62551,968
12/30/2022 15:01,44925.62575,54.94662099,,997,,12/30/2022 15:00,12/30/2022,15:00:54,1672412454,44925.62563,977
12/30/2022 15:01,44925.62576,54.76827627,,#N/A,,12/30/2022 15:01,12/30/2022,15:01:04,1672412464,44925.62574,983
12/30/2022 15:01,44925.62578,54.65414061,,#N/A,,12/30/2022 15:01,12/30/2022,15:01:14,1672412474,44925.62586,990
12/30/2022 15:01,44925.62579,54.6944848,,#N/A,,12/30/2022 15:01,12/30/2022,15:01:24,1672412484,44925.62597,992
12/30/2022 15:01,44925.6258,54.8655664,,#N/A,,12/30/2022 15:01,12/30/2022,15:01:34,1672412494,44925.62609,994
12/30/2022 15:01,44925.62581,54.99782958,,#N/A,,12/30/2022 15:01,12/30/2022,15:01:44,1672412504,44925.6262,996
12/30/2022 15:01,44925.62582,55.18331544,,#N/A,,12/30/2022 15:01,12/30/2022,15:01:54,1672412514,44925.62632,997
12/30/2022 15:01,44925.62583,55.79281249,,#N/A,,12/30/2022 15:02,12/30/2022,15:02:04,1672412524,44925.62644,997
12/30/2022 15:01,44925.62585,56.20864501,,#N/A,,12/30/2022 15:02,12/30/2022,15:02:14,1672412534,44925.62655,996
12/30/2022 15:01,44925.62586,56.5407373,,990,,12/30/2022 15:02,12/30/2022,15:02:24,1672412544,44925.62667,995
12/30/2022 15:01,44925.62587,60.0105873,,#N/A,,12/30/2022 15:02,12/30/2022,15:02:34,1672412554,44925.62678,993
12/30/2022 15:01,44925.62588,60.16300934,,#N/A,,12/30/2022 15:02,12/30/2022,15:02:44,1672412564,44925.6269,991
12/30/2022 15:01,44925.62589,60.31616206,,#N/A,,12/30/2022 15:02,12/30/2022,15:02:54,1672412574,44925.62701,989
12/30/2022 15:01,44925.6259,60.63929447,,#N/A,,12/30/2022 15:03,12/30/2022,15:03:04,1672412584,44925.62713,988
12/30/2022 15:01,44925.62591,60.75423433,,#N/A,,12/30/2022 15:03,12/30/2022,15:03:14,1672412594,44925.62725,985
12/30/2022 15:01,44925.62593,60.66025391,,#N/A,,12/30/2022 15:03,12/30/2022,15:03:24,1672412604,44925.62736,984
12/30/2022 15:01,44925.62594,60.80852824,,#N/A,,12/30/2022 15:03,12/30/2022,15:03:34,1672412614,44925.62748,980
12/30/2022 15:01,44925.62595,60.95832732,,#N/A,,12/30/2022 15:03,12/30/2022,15:03:44,1672412624,44925.62759,978
12/30/2022 15:01,44925.62596,61.01176655,,#N/A,,12/30/2022 15:03,12/30/2022,15:03:54,1672412634,44925.62771,977
12/30/2022 15:01,44925.62597,61.06651825,,992,,12/30/2022 15:04,12/30/2022,15:04:04,1672412644,44925.62782,975
12/30/2022 15:01,44925.62598,61.0574117,,#N/A,,12/30/2022 15:04,12/30/2022,15:04:14,1672412654,44925.62794,973
12/30/2022 15:01,44925.626,61.17908231,,#N/A,,12/30/2022 15:04,12/30/2022,15:04:24,1672412664,44925.62806,973
12/30/2022 15:01,44925.62601,61.41389327,,#N/A,,12/30/2022 15:04,12/30/2022,15:04:34,1672412674,44925.62817,973
12/30/2022 15:01,44925.62602,61.44865985,,#N/A,,12/30/2022 15:04,12/30/2022,15:04:44,1672412684,44925.62829,973
12/30/2022 15:01,44925.62603,61.54956095,,#N/A,,12/30/2022 15:04,12/30/2022,15:04:54,1672412694,44925.6284,972
12/30/2022 15:01,44925.62604,61.56395805,,#N/A,,12/30/2022 15:05,12/30/2022,15:05:04,1672412704,44925.62852,972
12/30/2022 15:01,44925.62605,61.61880563,,#N/A,,12/30/2022 15:05,12/30/2022,15:05:14,1672412714,44925.62863,970
12/30/2022 15:01,44925.62607,61.56815601,,#N/A,,12/30/2022 15:05,12/30/2022,15:05:24,1672412724,44925.62875,969
12/30/2022 15:01,44925.62608,61.69147388,,#N/A,,12/30/2022 15:05,12/30/2022,15:05:34,1672412734,44925.62887,968
12/30/2022 15:01,44925.62609,61.71420566,,994,,12/30/2022 15:05,12/30/2022,15:05:44,1672412744,44925.62898,969
12/30/2022 15:01,44925.6261,61.65684413,,#N/A,,12/30/2022 15:05,12/30/2022,15:05:54,1672412754,44925.6291,968
12/30/2022 15:01,44925.62611,61.5831496,,#N/A,,12/30/2022 15:06,12/30/2022,15:06:04,1672412764,44925.62921,967
12/30/2022 15:01,44925.62612,61.6114786,,#N/A,,12/30/2022 15:06,12/30/2022,15:06:14,1672412774,44925.62933,965
12/30/2022 15:01,44925.62613,61.66652792,,#N/A,,12/30/2022 15:06,12/30/2022,15:06:24,1672412784,44925.62944,964
12/30/2022 15:01,44925.62615,61.63769633,,#N/A,,12/30/2022 15:06,12/30/2022,15:06:34,1672412794,44925.62956,965
12/30/2022 15:01,44925.62616,61.72151206,,#N/A,,12/30/2022 15:06,12/30/2022,15:06:44,1672412804,44925.62968,963
12/30/2022 15:01,44925.62619,61.91181215,,#N/A,,12/30/2022 15:06,12/30/2022,15:06:54,1672412814,44925.62979,961
12/30/2022 15:01,44925.62619,61.8107549,,#N/A,,12/30/2022 15:07,12/30/2022,15:07:04,1672412824,44925.62991,961
12/30/2022 15:01,44925.62619,61.74530232,,#N/A,,12/30/2022 15:07,12/30/2022,15:07:14,1672412834,44925.63002,962
12/30/2022 15:01,44925.6262,61.78683456,,996,,12/30/2022 15:07,12/30/2022,15:07:24,1672412844,44925.63014,962
12/30/2022 15:01,44925.62622,61.98964498,,#N/A,,12/30/2022 15:07,12/30/2022,15:07:34,1672412854,44925.63025,961
12/30/2022 15:01,44925.62623,62.06869153,,#N/A,,12/30/2022 15:07,12/30/2022,15:07:44,1672412864,44925.63037,962
12/30/2022 15:01,44925.62624,62.0087326,,#N/A,,12/30/2022 15:07,12/30/2022,15:07:54,1672412874,44925.63049,961
12/30/2022 15:01,44925.62625,62.00978379,,#N/A,,12/30/2022 15:08,12/30/2022,15:08:04,1672412884,44925.6306,961
12/30/2022 15:01,44925.62626,62.14125689,,#N/A,,12/30/2022 15:08,12/30/2022,15:08:14,1672412894,44925.63072,961
12/30/2022 15:01,44925.62627,62.24624959,,#N/A,,12/30/2022 15:08,12/30/2022,15:08:24,1672412904,44925.63083,961
12/30/2022 15:01,44925.62629,62.39955239,,#N/A,,12/30/2022 15:08,12/30/2022,15:08:34,1672412914,44925.63095,961
12/30/2022 15:01,44925.6263,62.43986351,,#N/A,,12/30/2022 15:08,12/30/2022,15:08:44,1672412924,44925.63106,961
12/30/2022 15:01,44925.62631,62.47202071,,#N/A,,12/30/2022 15:08,12/30/2022,15:08:54,1672412934,44925.63118,960
12/30/2022 15:01,44925.62632,62.44233728,,997,,12/30/2022 15:09,12/30/2022,15:09:08,1672412948,44925.63134,960
12/30/2022 15:01,44925.62633,62.56210278,,#N/A,,12/30/2022 15:09,12/30/2022,15:09:18,1672412958,44925.63146,959
12/30/2022 15:01,44925.62634,62.85127307,,#N/A,,12/30/2022 15:09,12/30/2022,15:09:28,1672412968,44925.63157,960

CodePudding user response：

You could try to round the time to the closest 10 seconds on both sides.

Then you make a normal Vlookup.

If you have different dates in the data, you could concatenate date and Mrounded time on both sides to make the Vlookup

Q2  =MROUND(O2;"00:00:10")
X2  =MROUND(T2;"00:00:10")
R2  =VLOOKUP(Q2;$X$2:$Y$58;2;FALSE)