Value Extraction

[Steve 1962]

Hi
I have a list of dates & values (columns D & E). I have lookup values (column A) that refer to the dates in the list and values are extracted from column E, according to the lookup date.

The problem I have is that not all dates in column D are consecutive. In the example, there is no 25/1/25 in the list in column D.

The solution that I need is that if the date does not exist in column D, then give the value from column E that is closest to the Lookup Date in column A (preceding closet value). In this case, the extracted value would be $52,466 (associated with 24/1/25). Formulas needed in column B.

Thanks
S

Book1
	A	B	C	D	E
1	Lookup Date	Value		Date1	List
2	14/01/2025	$54,319		10/01/2025	$54,305
3	25/01/2025	$52,466		13/01/2025	$54,312
4	11/02/2025	$25,111		14/01/2025	$54,319
5				15/01/2025	$54,341
6				16/01/2025	$54,349
7				17/01/2025	$54,356
8				20/01/2025	$54,363
9				21/01/2025	$54,371
10				22/01/2025	$54,392
11				23/01/2025	$54,400
12				24/01/2025	$52,466
13				28/01/2025	$52,473
14				29/01/2025	$52,480
15				30/01/2025	$52,508
16				31/01/2025	$52,515
17				3/02/2025	$52,522
18				4/02/2025	$52,529
19				5/02/2025	$52,550
20				6/02/2025	$52,557
21				7/02/2025	$50,620
22				10/02/2025	$65,222
23				11/02/2025	$25,111
24				12/02/2025	$35,222
25				13/02/2025	$65,412
Sheet1

 

[SunnyAlv]

try

=XLOOKUP(A2,D:D,E:E,XLOOKUP(A2,D:D,E:E,,-1))

 

[SunnyAlv]

lookup with specific nearest date less/more, try :

=LET(dt,A2,DD,$D$1:$D$100,EE,$E$1:$E$100,a,XLOOKUP(dt,DD,DD,,1),b,XLOOKUP(dt,DD,DD,,-1),XLOOKUP(dt+MIN(a-dt,b-dt),DD,EE))

 

[Steve 1962]

Hey thanks SunnyAlv

Works fine.

Have a nice day.

S

 

[Peter_SSs]

if the date does not exist in column D, then give the value from column E that is closest to the Lookup Date in column A (preceding closet value).

try

=XLOOKUP(A2,D:D,E:E,XLOOKUP(A2,D:D,E:E,,-1))

There is no need for a double XLOOKUP to achieve the request.

25 02 13.xlsm
	A	B	C	D	E
1	Lookup Date	Value		Date1	List
2	14/01/2025	$54,319		10/01/2025	$54,305
3	25/01/2025	$52,466		13/01/2025	$54,312
4	11/02/2025	$25,111		14/01/2025	$54,319
5				15/01/2025	$54,341
6				16/01/2025	$54,349
7				17/01/2025	$54,356
8				20/01/2025	$54,363
9				21/01/2025	$54,371
10				22/01/2025	$54,392
11				23/01/2025	$54,400
12				24/01/2025	$52,466
13				28/01/2025	$52,473
14				29/01/2025	$52,480
15				30/01/2025	$52,508
16				31/01/2025	$52,515
17				3/02/2025	$52,522
18				4/02/2025	$52,529
19				5/02/2025	$52,550
20				6/02/2025	$52,557
21				7/02/2025	$50,620
22				10/02/2025	$65,222
23				11/02/2025	$25,111
24				12/02/2025	$35,222
25				13/02/2025	$65,412
XLOOKUP

Cell Formulas
Range	Formula
B2:B4	B2	=XLOOKUP(A2,D:D,E:E,,-1)

 

[SunnyAlv]

There is no need for a double XLOOKUP to achieve the request.

25 02 13.xlsm
	A	B	C	D	E
1	Lookup Date	Value		Date1	List
2	14/01/2025	$54,319		10/01/2025	$54,305
3	25/01/2025	$52,466		13/01/2025	$54,312
4	11/02/2025	$25,111		14/01/2025	$54,319
5				15/01/2025	$54,341
6				16/01/2025	$54,349
7				17/01/2025	$54,356
8				20/01/2025	$54,363
9				21/01/2025	$54,371
10				22/01/2025	$54,392
11				23/01/2025	$54,400
12				24/01/2025	$52,466
13				28/01/2025	$52,473
14				29/01/2025	$52,480
15				30/01/2025	$52,508
16				31/01/2025	$52,515
17				3/02/2025	$52,522
18				4/02/2025	$52,529
19				5/02/2025	$52,550
20				6/02/2025	$52,557
21				7/02/2025	$50,620
22				10/02/2025	$65,222
23				11/02/2025	$25,111
24				12/02/2025	$35,222
25				13/02/2025	$65,412
XLOOKUP

Cell Formulas
Range	Formula
B2:B4	B2	=XLOOKUP(A2,D:D,E:E,,-1)

thanks for advice, I have a concern, so I locked the first lookup with a matching value.

 

[Peter_SSs]

I have a concern, so I locked the first lookup with a matching value.

The -1 argument uses an exact match if there is one. From the XLOOKUP Help ..

 

[Steve 1962]

Thanks Peter. I have amended as per your recommendation and it works fine.

Appreciate your time.

Steve

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, you could do them all at once without copying the formula down

25 02 13.xlsm
	A	B	C	D	E
1	Lookup Date	Value		Date1	List
2	14/01/2025	$54,319		10/01/2025	$54,305
3	25/01/2025	$52,466		13/01/2025	$54,312
4	11/02/2025	$25,111		14/01/2025	$54,319
5				15/01/2025	$54,341
6				16/01/2025	$54,349
7				17/01/2025	$54,356
8				20/01/2025	$54,363
9				21/01/2025	$54,371
10				22/01/2025	$54,392
11				23/01/2025	$54,400
12				24/01/2025	$52,466
13				28/01/2025	$52,473
14				29/01/2025	$52,480
15				30/01/2025	$52,508
16				31/01/2025	$52,515
17				3/02/2025	$52,522
18				4/02/2025	$52,529
19				5/02/2025	$52,550
20				6/02/2025	$52,557
21				7/02/2025	$50,620
22				10/02/2025	$65,222
23				11/02/2025	$25,111
24				12/02/2025	$35,222
25				13/02/2025	$65,412
XLOOKUP

Cell Formulas
Range	Formula
B2:B4	B2	=XLOOKUP(A2:A4,D:D,E:E,,-1)
Dynamic array formulas.