Nearest date based on three criteria

[chris_bosten]

Hi,

I have 3 criteria: Vehicle, Model and Year

In the first table I have a date "Date 1"

In the second table I have a different date "Date 2"

Vehicle (A)	Model (B)	Year (C)	Date1 (D)	New Date (E)	(F)	Vehicle (G)	Model (H)	Year (I)	Date2 (J)
Ford	Mondeo	1992​	16/08/2024​	17/08/2024​		Ford	Mondeo	1992​	17/08/2024​
				#N/A​		Vauxhall	Leo	1993​	19/08/2024​
						Ford	Escort	1995​	15/08/2024​

I want "New Date" to return the date in Date 2 that is closest to date 1 when vehicle, model and year are matched in the two tables.

Based on an old thread I have a formula that works in E2 for one criteria, vehicle alone:

=INDEX(J3:J5,MATCH(MIN(IF(G3:G5=A3,IF(J3:J5<>D3,ABS(J3:J5-D3)))),IF(G3:G5=A3,IF(J3:J5<>D3,ABS(J3:J5-D3))),0))

However, I have tried an IF(AND logic to add an additional criteria (Model), but I get an #NA result:

=INDEX(J3:J5,MATCH(MIN(IF(AND(G3:G5=A3,H3:H5=B3),IF(J3:J5<>D3,ABS(J3:J5-D3)))),IF(AND(G3:G5=A3,H3:H5=B3),IF(J3:J5<>D3,ABS(J3:J5-D3))),0))

What am I doing wrong?

Best wishes

 

[hagia_sofia]

Hello, with 365 and presuming that "closest" means in both directions you could e.g. use:

=LET(
a,FILTER(J3:J7,(G3:G7=A3)*(H3:H7=B3)*(I3:I7=C3)),
b,ABS(a-D3),
UNIQUE(FILTER(a,b=MIN(b))))

 

[chris_bosten]

Wow, thank you!