using vlookup to lookup a date within multiple date ranges

[fedebrin]

Hello,

I am trying to extract a value from a list that has a data range and a parameter (ie color)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1-Jan-19[/TD]
[TD]28-Feb-19[/TD]
[TD]Red[/TD]
[TD]Value 1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1-Mar-19[/TD]
[TD]31-Mar-19[/TD]
[TD]Red[/TD]
[TD]Value 2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1-Jan-19[/TD]
[TD]31-Mar-19[/TD]
[TD]Blue[/TD]
[TD]Value 3[/TD]
[/TR]
</tbody>[/TABLE]

Considering I am entering the color under cell F6 and the date under cell G6, I am using the following formula but not getting the correct answer:

{=INDEX($D$1:$D$3,MATCH(1,IF(G6>=$A$1:$A$3,IF(G6<=$B$1:$B$3,IF(F6=$C$1:$C$3,1)),0)))}


Your help is really appreciated!

 

[AhoyNC]

Try:

=INDEX($D$1:$D$3,MATCH(1,IF(G6>=$A$1:$A$3,IF(G6<=$B$1:$B$3,IF(F6=$C$1:$C$3,1))[COLOR=#ff0000]),0[/COLOR]))

Excel Workbook
	A	B	C	D	E	F	G
1	1-Jan-19	28-Feb-19	Red	Value 1
2	1-Mar-19	31-Mar-19	Red	Value 2
3	1-Jan-19	31-Mar-19	Blue	Value 3		Value 3
4
5
6						blue	1/5/2019
Sheet

 

[fedebrin]

Excellent! this seems to be working perfectly.

Will let you know if any issues

 

[AhoyNC]

You're welcome. Thanks for the feedback.
If you have Excel 2010 or later you could also use the formula below that dosn't require CTRL-SHIFT-ENTER. Just ENTER.

Excel Workbook
	A	B	C	D	E	F	G
1	1-Jan-19	28-Feb-19	Red	Value 1
2	1-Mar-19	31-Mar-19	Red	Value 2
3	1-Jan-19	31-Mar-19	Blue	Value 3		Value 3
4
5
6						blue	1/5/2019
Sheet

 

[DanteAmor]

A "Regular" formula, Suppose you have a header in row 1

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80.79px;" /><col style="width:80.79px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:90.3px;" /><col style="width:82.69px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Date1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Date1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Color</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Value</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">01/01/2019</td><td style="text-align:right; ">28/02/2019</td><td >green</td><td >Value 1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01/03/2019</td><td style="text-align:right; ">31/03/2019</td><td >Red</td><td >Value 2</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">01/01/2019</td><td style="text-align:right; ">31/03/2019</td><td >Blue</td><td >Value 3</td><td > </td><td >Value 3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >blue</td><td style="text-align:right; ">05/01/2019</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >F4</td><td >=IFERROR(INDEX(D2:D4,SUMPRODUCT((A2:A4<=G7)*(B2:B4>=G7)*(C2:C4=F7)*(ROW(D2:D4)))-1),"Does not exist")</td></tr></table></td></tr></table>