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]

You didn't answer the question. Are you using a structured reference table, where a formula can include references like [Temp] or [@Temp]?

And when you say the formula "didn't work", does that mean the cell wouldn't accept the formula? Or it did accept the formula, but it produced an error? Or wrong results?

 

[alvintranvcu123]

I copied your format, pasted to excel file.

 

[StephenCrump]

With cell references ...

	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
Sheet1

Cell Formulas
Range	Formula
G3:G9	G3	=LET(T2Time,B$3:B$8,T2Temp,C$3:C$8,T1Time,E$3:E$9,T1Temp,F$3:F$9,A,BYROW(T1Time,LAMBDA(r,LET(a,ABS(T2Time-r),XLOOKUP(MIN(a),a,T2Temp)))),B,BYROW(T2Time,LAMBDA(r,LET(a,ABS(T1Time-r),XLOOKUP(MIN(a),a,T1Temp)))),IF(MMULT(--(A=TRANSPOSE(A)),SEQUENCE(ROWS(A),,,0))=1,A,IF(XLOOKUP(A,T2Temp,B)=T1Temp,A,"")))
Dynamic array formulas.

 

[alvintranvcu123]

Oh, It's work very well. So glad. sorry for my poor knowledge of Excel. Now, I've just copied & pasted data to your format.
Thanks so much.
How can I thank you?

 

[StephenCrump]

Thanks so much.
How can I thank you?

You just did. I'm glad it's working for you.

 

[alvintranvcu123]

I tried using bigger temp data, but it won't work. Too much Thread to process.

 

[alvintranvcu123]

What should I do for the next rows?

 

[alvintranvcu123]

 

[StephenCrump]

In Post #13, G3 is a single-cell formula. You don't need to copy it down the column. That's why you're getting the #SPILL! errors.

Let me know if you still have thread processing problems, as we can make the formula more efficient and/or use helper cells.

How many rows do you have in your actual tables? Will the times always be sorted in ascending order, as you've shown above?

 

[Cubist]

See if this is faster for you. Similar to StephenCrump's, it's an array formula so insert in the first cell only and clear out everything below.

Book1
	B	C	D	E	F	G	H
1	Table2			Table1
2	Time	Temp		Time	Temp	Table 2 temp
3	10	A		9	P	A	<-Insert formula here only
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
Sheet1

Cell Formulas
Range	Formula
G3:G9	G3	=LET( t,TOROW(C3:C8),a,ABS(TOROW(B3:B8)-E3:E9),r,ROWS(a), b,BYROW(IF((SEQUENCE(r)=r)+(a=BYROW(a,MIN))*(a=BYCOL(a,MIN)),t,""),LAMBDA(x,XLOOKUP(TRUE,x<>"",x,"",,-1))), IF(XMATCH(b,b)=SEQUENCE(ROWS(b)),b,"") )
Dynamic array formulas.