Vlookup with multiple criteria + skip blank and find the next value

[Jana excel]

I want to do a vlookup on a range where I have duplicate values in Column A & B and I want to return the values in column C
Also if the values is blank, I want to search in the next row.

Region	Responsible	Next appointment
Malvern	KZ	01/01/2024
East Burwood	KZ
East Burwood	SE	05/06/2024
Southland	KZ
Southland	PT	15/07/2024
Southland	SE
Southland	SE	17/06/2024

I want my formula to return the date that Responsible "SE" will visit region "Southland" and not the blank value -> 17/06/2024
When I use formula: =INDEX($C$19:$C$25;MATCH(D19;IF(ISNUMBER($C$19:$C$25);$A$19:$A$25);0)) the result is: 15/07/2024, it takes the first date for that region (so not taken into account the responsible)
When I use formula: =INDEX($C$19:$C$25;MATCH(D19&D18;A19:A25&B19:B25;0)) the result = blank (0/01/1900 in date format)

I would need a combination of those two formulas.
I want an index match formula that will match D19&D18 with column A & B, and the result should be a number (date)

Does anyone have a solution or formula that can resolve my problem?

Thanks

 

[StephenCrump]

Welcome to the Forum!

Perhaps something like this:

	A	B	C	D	E	F	G
1	Region	Responsible	Next appointment		Region	Responsible	Next appointment
2	Malvern	KZ	1 Jan 2024		Malvern	KZ	1 Jan 2024
3	East Burwood	KZ			East Burwood	KZ
4	East Burwood	SE	5 Jun 2024		East Burwood	SE	5 Jun 2024
5	Southland	KZ			Southland	KZ
6	Southland	PT	15 Jul 2024		Southland	PT	15 Jul 2024
7	Southland	SE			Southland	SE
8	Southland	SE	17 Jun 2024		Southland	SE	17 Jun 2024
9					Southland	SE	1 Jul 2024
10	FIND				Southland	SE	24 Jun 2024
11	Southland	SE	17 Jun 2024
12					FIND
13					Southland	SE	24 Jun 2024
Sheet1

Cell Formulas
Range	Formula
C11	C11	=FILTER(C2:C8,(A2:A8=A11)*(B2:B8=B11)*LEN(C2:C8),"n/a")
G13	G13	=TAKE(SORT(FILTER(G2:G10,(E2:E10=E13)*(F2:F10=F13)*(G2:G10>=TODAY()),"n/a")),1)

The first example is based on your simple dataset you posted. The second picks the first appointment on or after TODAY(), where today it's 20 June 2024.