VLOOKUP Multiple Criteria

[cookeetree]

G'day Excel Gods,

I need to look up a value based on multiple criteria.

In column A, I have a list of training course numbers and in column C I have a list of dates of those courses, past and future. If an employee's training is overdue, I want the formula to find the date of the NEXT training course. A standard VLOOKUP just gives me the first date, which is in the past.

Any help you could provide would be greatly appreciated.

Thanks, Jason.

 

[Toadstool]

Hi Cookeetree,

Does this do what you want?

Book1
	A	B	C	D	E	F	G	H
1	Course Number	Title	Date			Course Needed		Next Date
2	CX748	Hamster Grooming	5/6/2020			CX748	Hamster Grooming	4/16/2020
3	CX245	Mole Wrangling	12/30/2018
4	CX463	Turtle Tickling Level 3	11/27/2020
5	CX245	Mole Wrangling	12/6/2019
6	CX463	Turtle Tickling Level 3	7/8/2018
7	CX748	Hamster Grooming	9/16/2019
8	CX245	Mole Wrangling	4/13/2018
9	CX463	Turtle Tickling Level 3	6/12/2020
10	CX748	Hamster Grooming	4/16/2020
11	CX245	Mole Wrangling	3/21/2020
12	CX463	Turtle Tickling Level 3	10/30/2020
13	CX748	Hamster Grooming	5/2/2019
14	CX463	Turtle Tickling Level 3	10/17/2019
15
Sheet1

Cell Formulas
Range	Formula
G2	G2	=IFERROR(INDEX(B:B,MATCH($F$2,$A$1:$A$999,0)),"")
H2	H2	=IFERROR(AGGREGATE(15,6,$C$2:$C$999/(($C$2:$C$999>TODAY())*($A$2:$A$999=$F$2)),1),"")

 

[teylyn]

And with Dynamic Array formula FILTER()

=MIN(FILTER(C:C,(A:A=F2)*(C:C>TODAY())))

Available in Excel 365 only.

 

[cookeetree]

Thank you both. Awesome work!