Vlookup with 2 criteria

[emshep005]

I need help with a formula to look in a chart & (1) identify brand, (2) determine if a date is between to dates, and (3) return a value of a corresponding event. Here is a snapshot of the data:

[TABLE="width: 396"]
<tbody>[TR]
[TD="class: xl66, width: 101"]Brand (Col. A)[/TD]
[TD="class: xl66, width: 224"]Event (Col. B)[/TD]
[TD="class: xl67, width: 101"]start (Col. C)[/TD]
[TD="class: xl67, width: 101"]end (Col. D)[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD="class: xl65"]Winter Clearance[/TD]
[TD="class: xl68, align: right"]1/1/2017[/TD]
[TD="class: xl68, align: right"]2/7/2017[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD="class: xl65"]Athletic[/TD]
[TD="class: xl68, align: right"]2/8/2017[/TD]
[TD="class: xl68, align: right"]3/28/2017[/TD]
[/TR]
[TR]
[TD="class: xl69"]Brand B[/TD]
[TD="class: xl65"]Spring[/TD]
[TD="class: xl68, align: right"]3/29/2017[/TD]
[TD="class: xl68, align: right"]5/23/2017[/TD]
[/TR]
[TR]
[TD="class: xl69"]Brand B[/TD]
[TD="class: xl65"]Summer[/TD]
[TD="class: xl68, align: right"]5/24/2017[/TD]
[TD="class: xl68, align: right"]6/20/2017[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD="class: xl65"]Summer Clearance[/TD]
[TD="class: xl68, align: right"]6/6/2018[/TD]
[TD="class: xl68, align: right"]7/10/2018[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD="class: xl65"]Black Friday[/TD]
[TD="class: xl68, align: right"]11/14/2018[/TD]
[TD="class: xl68, align: right"]11/27/2018[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD="class: xl65"]Holiday[/TD]
[TD="class: xl68, align: right"]11/28/2018[/TD]
[TD="class: xl68, align: right"]1/1/2019[/TD]
[/TR]
[TR]
[TD="class: xl69"]Brand B[/TD]
[TD="class: xl65"]Spring[/TD]
[TD="class: xl70, align: right"]3/14/2018[/TD]
[TD="class: xl70, align: right"]5/8/2018[/TD]
[/TR]
[TR]
[TD="class: xl69"]Brand B[/TD]
[TD="class: xl65"]Summer[/TD]
[TD="class: xl70, align: right"]5/9/2018[/TD]
[TD="class: xl70, align: right"]6/19/2018[/TD]
[/TR]
</tbody>[/TABLE]


Here is what I'm looking for:

[TABLE="width: 228"]
<tbody>[TR]
[TD="class: xl65, width: 101"]Brand[/TD]
[TD="class: xl65, width: 101"]Actual Date[/TD]
[TD="class: xl65, width: 101"]Event[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brand A[/TD]
[TD="class: xl66"]3/15/2017[/TD]
[TD="class: xl67"]?[/TD]
[/TR]
[TR]
[TD="class: xl65"]Brand B[/TD]
[TD="class: xl66"]5/18/2018[/TD]
[TD="class: xl67"]?[/TD]
[/TR]
</tbody>[/TABLE]


So this first one for Brand A 3/15/17 should return event: Athletic
Brand B 5/18/18 should return event: Summer



Thank you!!!

 

[Kamolga]

Re: Need help with Vlookup with 2 criteria

Book1
	A	B	C	D
1	Brand	Event	start	end
2	Brand A	Winter Clearance	01/01/2017	07/02/2017
3	Brand A	Athletic	08/02/2017	3/28/2017
4	Brand B	Spring	3/29/2017	5/23/2017
5	Brand B	Summer	5/24/2017	6/20/2017
6	Brand A	Summer Clearance	06/06/2018	10/07/2018
7	Brand A	Black Friday	11/14/2018	11/27/2018
8	Brand A	Holiday	11/28/2018	01/01/2019
9	Brand B	Spring	3/14/2018	08/05/2018
10	Brand B	Summer	09/05/2018	6/19/2018
11
12	Brand	Actual Date	Event
13	Brand A	3/15/2017	Athletic
14	Brand B	5/18/2018	Summer
Test

Cell Formulas
Range	Formula
C13	{=INDEX($B$2:$B$10,MATCH(B13,IF($C$2:$C$10<=B13,IF($A$2:$A$10=A13,IF($D$2:$D$10>=B13,$D$2:$D$10))),-1))}
C14	{=INDEX($B$2:$B$10,MATCH(B14,IF($C$2:$C$10<=B14,IF($A$2:$A$10=A14,IF($D$2:$D$10>=B14,$D$2:$D$10))),-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Kamolga]

Re: Need help with Vlookup with 2 criteria

Sorry, posted twice.

 

[emshep005]

Re: Need help with Vlookup with 2 criteria

That worked! thank you!!

 

[Kamolga]

Re: Need help with Vlookup with 2 criteria

You are welcome. Thanks for the feedback!