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

 

[StephenCrump]

Welcome to the Forum!

Your question isn't clear. Is this what you want to do - find the closest matching time in Table 1 for each time in Table2?

	A	B	C	D	E	F	G	H
1	Table1			Table2
2	Time	Temperature		Time	Temperature	Table1 time	Table1 temperature
3	10:04:04 AM	1455		10:01:59 AM	1565	10:04:04 AM	1455
4	10:04:14 AM	1463		10:04:07 AM	1548	10:04:04 AM	1455
5	10:04:24 AM	1458		10:05:30 AM	1568	10:05:14 AM	1504
6	10:04:34 AM	1463		10:06:00 AM	1564	10:05:14 AM	1504
7	10:04:44 AM	1472		10:07:27 AM	1566	10:05:14 AM	1504
8	10:04:54 AM	1487		10:08:27 AM	1535	10:05:14 AM	1504
9	10:05:04 AM	1496		10:09:31 AM	1545	10:05:14 AM	1504
10	10:05:14 AM	1504		10:09:57 AM	1545	10:05:14 AM	1504
11
Sheet1

Cell Formulas
Range	Formula
F3:F10	F3	=LET(t,ABS(Table1[Time]-[@Time]),XLOOKUP(MIN(t),t,Table1[Time]))
G3:G10	G3	=XLOOKUP([@[Table1 time]],Table1[Time],Table1[Temperature])

 

[alvintranvcu123]

Table 1 is fixed.
I would like to keep all data on table 1, add one new column on the table 1. Look up the temp from table 2 to new column.

If time is the exact, add this temp (table 2) in new column.
if time is different, add the temp (table 2) is closest the time in table 1.

Table 1 From table 2
10:33:04 1509 1508 ( exact)
10:35:26 1527 1523 ( measured at 10.34:59 but closest to 10:35:26 in table 1, so take it )

 

[alvintranvcu123]

 

[StephenCrump]

So like this then?

	A	B	C	D	E	F
1	Table2			Table1
2	Time	Temperature		Time	Temperature	Table2 temperature
3	10:01:59 AM	1565		10:00:00 AM	1455	1565
4	10:04:07 AM	1548		10:01:00 AM	1463	1565
5	10:05:30 AM	1568		10:02:00 AM	1458	1565
6	10:06:00 AM	1564		10:03:00 AM	1463	1565
7	10:07:27 AM	1566		10:04:00 AM	1472	1548
8	10:08:27 AM	1535		10:05:00 AM	1487	1568
9	10:09:31 AM	1545		10:08:00 AM	1496	1535
10	10:09:57 AM	1545		10:10:00 AM	1504	1545
Sheet1

Cell Formulas
Range	Formula
F3:F10	F3	=LET(t,ABS(Table2[Time]-[@Time]),XLOOKUP(MIN(t),t,Table2[Temperature]))

If you want to allow for the possibility of two matching closest times, you could use:

=LET(t,ABS(Table2[Time]-[@Time]),AVERAGE(FILTER(Table2[Temperature],MIN(t)=t)))

 

[alvintranvcu123]

Look cool.

The closest time= I'd like to take 01 temp value only.

10:01:59 AM (1565), in table 1 it should only show in row 10:02:00 ( not others) because only closest.

 

[StephenCrump]

I hope I'm not over-complicating this (and it would be simpler to use helper columns) ....

	A	B	C	D	E	F	G
1		Table2			Table1
2		Time	Temp		Time	Temp	Table 2 temp
3		10	A		9	P	A
4		12	B		15	Q	C
5		16	C		19	R	D
6		20	D		21	S
7		30	E		26	T
8		35	F		31	U	E
9					41	V	F
10
Sheet1

Cell Formulas
Range	Formula
G3:G9	G3	=LET(A,BYROW([Time],LAMBDA(r,LET(a,ABS(Table2[Time]-r),XLOOKUP(MIN(a),a,Table2[Temp])))),B,BYROW(Table2[Time],LAMBDA(r,LET(a,ABS([Time]-r),XLOOKUP(MIN(a),a,[Temp])))),INDEX(IF(MMULT(--(A=TRANSPOSE(A)),SEQUENCE(ROWS(A),,,0))=1,A,IF(XLOOKUP(A,Table2[Temp],B)=[Temp],A,"")),ROW([@Time])-ROW(Table1[[#Headers],[Time]])))

I've used numbers rather than times, and A,B,C etc rather than numbers for the temperature, just to make it easier to check. I haven't done much testing.

 

[alvintranvcu123]

Thanks so much. How do I copy your formula to our excel file?
This formula is too complex.

I would like to do the format, then just paste new data & done.

 

[StephenCrump]

This formula is too complex.

I have been using structured references all along.

It looks like you're not using structured reference tables?

 

[alvintranvcu123]

I mean I copy your formula to our Excel cell, but it's not work. Your formula is beyond my knowledge, so I'm happy to copy your formula, paste to my cell is OK.

But it didn't work.