Find the value for the exact or closest time

[alvintranvcu123]

Dear all/

I have the temperature data, which were measured from 02 sources. The measuring times may be different. They could be the same or near the same in 02 sources.
I would
I would like to make it a table.

1. Exact time: show 02 temp values

2. Not exact time: show 02 temp which one temp is the closest time in table 1.

( 1)

10:04:04 AM	1455
10:04:14 AM	1463
10:04:24 AM	1458
10:04:34 AM	1463
10:04:44 AM	1472
10:04:54 AM	1487
10:05:04 AM	1496
10:05:14 AM	1504

(2)

10:01:59 AM	1565
10:04:07 AM	1548
10:05:30 AM	1568
10:06:00 AM	1564
10:07:27 AM	1566
10:08:27 AM	1535
10:09:31 AM	1545
10:09:57 AM	1545

 

[alvintranvcu123]

Dear.
I have big data for table 1.
+Range table 2: C3:C102, B3:B102
+Range table 1: E3:E2471

I tried to expand the range for your formula, but it still won't work. sorry for my poor Excel skill

 

[Cubist]

It appears that you need to explicitly define the LAMBDA function in the BYCOL/BYROW in some version of XL. Try:

=LET(
t,TOROW(C3:C102),a,ABS(TOROW(B3:B102)-E3:E2471),r,ROWS(a),
BYROW(IF((SEQUENCE(r)=r)+(a=BYROW(a,LAMBDA(x,MIN(x))))*(a=BYCOL(a,LAMBDA(x,MIN(x)))),t,""),LAMBDA(x,XLOOKUP(TRUE,x<>"",x,"",,-1)))
)

 

[alvintranvcu123]

Oh, it works well.

still has a small issue.

Table 2 time format is 1/16/2025 10:18:26 AM
Table 1 time format is 10:18:26 AM

So the formula didn't work unless I erase date.
I changed the format but still didn't work. How can I remove the date "1/16/2025" from table 2 to become exact same time format as table 1.

 

[Cubist]

Try adding MOD.

=LET(
t,TOROW(C3:C102),a,ABS(TOROW(MOD(B3:B102,1))-E3:E2471),r,ROWS(a),
BYROW(IF((SEQUENCE(r)=r)+(a=BYROW(a,LAMBDA(x,MIN(x))))*(a=BYCOL(a,LAMBDA(x,MIN(x)))),t,""),LAMBDA(x,XLOOKUP(TRUE,x<>"",x,"",,-1)))
)