two way index match with approximate date match

[Kmitchell]

Having difficulty with setting up this formula to work properly. Essentially I need to match on row (Model) and approximate date.

In this example, Model A's closest date to 3/31/2023 is 3/30/2023 and the return value should be 87,800. I tried the below formula but its not working. Any suggestions are greatly appreciated.

NDEX($B$2:$K$15,MATCH($A22,IF($B$1:$K$1=B$21,$A$2:$A$15,1)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also your expected answer do not match your data.

 

[Kmitchell]

Here is the revised expected results

 

[JamesCanale]

I think this aught to work with an older version of excel if that is what you have.

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	A	B	C	D	E	F	G	H	I	J	K
2	3/1/2023	267,000	94,000	220,000	266,000	275,000	43,000	400,000	84,000	69,000	448,000	446,000
3	3/29/2023	264,000	330,000	36,000	178,000	363,000	221,000	165,000	475,000	457,000	113,000	391,000
4	3/30/2023	359,000	220,000	368,000	247,000	2,000	343,000	388,000	198,000	379,000	77,000	90,000
5	3/31/2023	476,000	348,000	455,000	488,000	59,000	23,000	82,000	462,000	316,000	406,000	422,000
6	4/1/2023	275,000	71,000	388,000	475,000	266,000	366,000	214,000	339,000	113,000	97,000	411,000
7	4/6/2023	171,000	193,000	290,000	32,000	335,000	262,000	31,000	69,000	436,000	467,000	7,000
8	4/7/2023	140,000	196,000	403,000	58,000	153,000	300,000	69,000	70,000	241,000	227,000	280,000
9	4/8/2023	49,000	488,000	419,000	79,000	3,000	141,000	433,000	79,000	433,000	192,000	450,000
10	4/9/2023	357,000	242,000	181,000	303,000	245,000	148,000	287,000	414,000	86,000	424,000	131,000
11	4/26/2023	88,000	2,000	414,000	71,000	98,000	37,000	377,000	281,000	141,000	213,000	332,000
12	4/27/2023	61,000	245,000	22,000	349,000	199,000	45,000	56,000	19,000	463,000	266,000	149,000
13	4/28/2023	295,000	161,000	353,000	411,000	250,000	31,000	352,000	248,000	481,000	235,000	110,000
14	4/29/2023	49,000	272,000	279,000	499,000	150,000	19,000	158,000	131,000	29,000	417,000	77,000
15	4/30/2023	500,000	252,000	180,000	155,000	300,000	216,000	467,000	24,000	457,000	373,000	414,000
16
17
18	Date	Closest Date	A	B	C
19	3/31/2023	3/31/2023	476,000	348,000	455,000
20	4/30/2023	4/30/2023	500,000	252,000	180,000
21	3/15/2023	3/1/2023	267,000	94,000	220,000
22	3/16/2023	3/29/2023	264,000	330,000	36,000
Sheet25

Cell Formulas
Range	Formula
B19:B22	B19	=INDEX($A$2:$A$15,MATCH(MIN(ABS($A$2:$A$15-A19)),ABS($A$2:$A$15-A19),0))
C19:E22	C19	=INDEX($B$2:$L$15,MATCH($B19,$A$2:$A$15,0),MATCH(C$18,$B$1:$L$1,0))

 

[Kmitchell]

I think this aught to work with an older version of excel if that is what you have.

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	A	B	C	D	E	F	G	H	I	J	K
2	3/1/2023	267,000	94,000	220,000	266,000	275,000	43,000	400,000	84,000	69,000	448,000	446,000
3	3/29/2023	264,000	330,000	36,000	178,000	363,000	221,000	165,000	475,000	457,000	113,000	391,000
4	3/30/2023	359,000	220,000	368,000	247,000	2,000	343,000	388,000	198,000	379,000	77,000	90,000
5	3/31/2023	476,000	348,000	455,000	488,000	59,000	23,000	82,000	462,000	316,000	406,000	422,000
6	4/1/2023	275,000	71,000	388,000	475,000	266,000	366,000	214,000	339,000	113,000	97,000	411,000
7	4/6/2023	171,000	193,000	290,000	32,000	335,000	262,000	31,000	69,000	436,000	467,000	7,000
8	4/7/2023	140,000	196,000	403,000	58,000	153,000	300,000	69,000	70,000	241,000	227,000	280,000
9	4/8/2023	49,000	488,000	419,000	79,000	3,000	141,000	433,000	79,000	433,000	192,000	450,000
10	4/9/2023	357,000	242,000	181,000	303,000	245,000	148,000	287,000	414,000	86,000	424,000	131,000
11	4/26/2023	88,000	2,000	414,000	71,000	98,000	37,000	377,000	281,000	141,000	213,000	332,000
12	4/27/2023	61,000	245,000	22,000	349,000	199,000	45,000	56,000	19,000	463,000	266,000	149,000
13	4/28/2023	295,000	161,000	353,000	411,000	250,000	31,000	352,000	248,000	481,000	235,000	110,000
14	4/29/2023	49,000	272,000	279,000	499,000	150,000	19,000	158,000	131,000	29,000	417,000	77,000
15	4/30/2023	500,000	252,000	180,000	155,000	300,000	216,000	467,000	24,000	457,000	373,000	414,000
16
17
18	Date	Closest Date	A	B	C
19	3/31/2023	3/31/2023	476,000	348,000	455,000
20	4/30/2023	4/30/2023	500,000	252,000	180,000
21	3/15/2023	3/1/2023	267,000	94,000	220,000
22	3/16/2023	3/29/2023	264,000	330,000	36,000
Sheet25

Cell Formulas
Range	Formula
B19:B22	B19	=INDEX($A$2:$A$15,MATCH(MIN(ABS($A$2:$A$15-A19)),ABS($A$2:$A$15-A19),0))
C19:E22	C19	=INDEX($B$2:$L$15,MATCH($B19,$A$2:$A$15,0),MATCH(C$18,$B$1:$L$1,0))

Thanks for supplying the detailed reply, this works but not if there is no data in a date, for example model C has nothing in 3/31/2023, but they do for 3/30/2023, we would want to pull the closest date prior to 3/31, so the result should be 87,800. It varies by each of the models.

 

[Fluff]

Your expected results still do not match the data.
Maybe

=LET(f,CHOOSECOLS(FILTER(B2:K15,A2:A15<=A18),MATCH(B17,B1:K1,0)),FILTER(f,f<>"","no data"))

 

[Kmitchell]

Your expected results still do not match the data.
Maybe

=LET(f,CHOOSECOLS(FILTER(B2:K15,A2:A15<=A18),MATCH(B17,B1:K1,0)),FILTER(f,f<>"","no data"))

 

[Kmitchell]

Your expected results still do not match the data.
Maybe

=LET(f,CHOOSECOLS(FILTER(B2:K15,A2:A15<=A18),MATCH(B17,B1:K1,0)),FILTER(f,f<>"","no data"))

Adjusted expected results adjusted are shown above.

Thank you for this suggestion.

FYI: I entered in this formula and it retuned #SPILL!

 

[Kmitchell]

Your expected results still do not match the data.
Maybe

=LET(f,CHOOSECOLS(FILTER(B2:K15,A2:A15<=A18),MATCH(B17,B1:K1,0)),FILTER(f,f<>"","no data"))

Here are the results vs expected results. Am I missing something?

 

[Fluff]

Oops, forgot a bit, try

=LET(f,CHOOSECOLS(FILTER($B$2:$K$15,$A$2:$A$15<=$A18),MATCH(B$17,$B$1:$K$1,0)),TAKE(FILTER(f,f<>"","no data"),-1))