Priority for Index Match

[Forty2]

Hi Folks,

I was wondering if there was a way to have priority into a multi-conditions Match.

Here is my issue below: I have 3 data columns : Port, Vessel and Time of Arrival.

Port	Vessel	Actual Arrival
BARCELONA	Cruise Barcelona	02/09/2022 18:58​
BARCELONA	Cruise Roma	04/09/2022 21:23​
BARCELONA	Cruise Barcelona	31/08/2022 17:31​

I want to match the Actual arrive with a criteria/target as below, where Port and Vessel can have have exact match, and Planned Arrival is not an exact match

Port	Vessel	Planned Arrival
BARCELONA	Cruise Barcelona	04/09/2022 19:00​

Therefore, at least in my opinion, there are two steps for this process :
1) Port and Vessel need to match - I can use an array Index Match for that all good. With Example above, row 1 and 3 will be ok.
2) Based on this shortlist of rows, i want to have the closest time possible to match, using MIN/ABS formula. Here, 04/09 is closest to 02/09, so row 1 should be selected.

My question is - is there a way to do this shortlist first? As if I try to have all conditions together in a single Index/Match, it would not work, as the closest matching row is row 2 where column 1 and 2 do not work.


Let me know if it's clear and if you need more details. Thanks for the support!

 

[JamesCanale]

MrExcelPlayground11.xlsx
	A	B	C
12	Port	Vessel	Actual Arrival
13	BARCELONA	Cruise Barcelona	9/2/2022 18:58
14	BARCELONA	Cruise Roma	9/4/2022 21:23
15	BARCELONA	Cruise Barcelona	8/31/2022 17:31
16
17	BARCELONA	Cruise Barcelona	9/4/2022 19:00
18
19		Closest Time	9/2/2022 18:58
Sheet25

Cell Formulas
Range	Formula
C19	C19	=SUMPRODUCT(--(IF((A13:A15=A17)*(B13:B15=B17),ABS(C13:C15-C17),99999)=(MIN(IF((A13:A15=A17)*(B13:B15=B17),ABS(C13:C15-C17),99999)))),C13:C15)

 

[Forty2]

Thanks a lot, brillant!