Return Values in a Column if date is between 2 columns

[RobbieJannoh914]

Hello,

This is my first time posting here and I've searched everywhere to find the resolution to my issue but was unable to find a solution.

I am trying to get B1 to bring back the value in Column F based on multiple criteria: (A1 is >= D:D and A1 <= E:E and C = "Collared")
If we do this formula, B1 should 4.00

Unfortunately I haven't been able to form a proper index match formula, or an indirect match formula. Is there a combination of formulas that I can use to get this solution?

Please advise.

Please see table below for example

[TABLE="width: 150"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3/1/2015[/TD]
[TD][/TD]
[TD]Collared[/TD]
[TD]1/1/2015[/TD]
[TD]1/31/2015[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Fixed[/TD]
[TD]2/1/2015[/TD]
[TD]2/28/2015[/TD]
[TD]4.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Collared[/TD]
[TD]3/1/2015[/TD]
[TD]3/31/2015[/TD]
[TD]4.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Collared[/TD]
[TD]4/1/2015[/TD]
[TD]4/30/2015[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Fixed[/TD]
[TD]5/1/2015[/TD]
[TD]5/31/2015[/TD]
[TD]3.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[FlameRetired]

Try array entering this formula in B1 and filling down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Excel 2012
	A	B	C	D	E	F
1	3/1/2015	4	Collared	1/1/2015	1/31/2015	3
2			Fixed	2/1/2015	2/28/2015	4
3			Collared	3/1/2015	3/31/2015	4
4			Collared	4/1/2015	4/30/2015	5
5			Fixed	5/1/2015	5/31/2015	3
Sheet2

Cell Formulas
Range	Formula
B1	{=IFERROR(INDEX($F$1:$F$1000,SMALL(IF(($A$1>=$D$1:$D$1000)*($A$1<=$E$1:$E$1000)*($C$1:$C$1000="Collared"),ROW($C$1:$C$1000)),ROWS($1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RobbieJannoh914]

Hi Flame,

Thank you so much for responding. Your solution worked great! But there is just one tweak that I needed to make, and I broke it... I am actually working on 2 separate sheets meaning Sheet 1 needs the array, but Sheet 2 holds all the data. Unfortunately it doesn't give me the same result if I insert 'Sheet2!' in front of the references. With the same data set, if you enter this arrow into cell B2 of sheet 1 you get "3" versus "4" if you type it out on sheet 2:

Sheet1!B2 = the array below which gains value of 3

{=IFERROR(INDEX(Sheet2!$F$1:$F1000,SMALL(IF(($A$1>=Sheet2!$D$1:$D$1000)*($A$1<=Sheet2!$E$1:$E$1000)*(Sheet4!$C$1:$C$2="Collared"),ROW(Sheet2!$C$1:$C$1000)),ROWS(Sheet2!$1:1000))),"")}

Excel 2012

Sheet1​

	A	B	C	D	E	F

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

	A	B	C	D	E	F
Collared
	Fixed
	Collared
	Collared
	Fixed

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1/31/2015[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2/28/2015[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]3/31/2015[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4/1/2015[/TD]
[TD="align: right"]4/30/2015[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]5/31/2015[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet2!B2 = the array below which gains value of 4

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]{=IFERROR(INDEX($F$1:$F$1000,SMALL(IF(($A$1>=$D$1:$D$1000)*($A$1<=$E$1:$E$1000)*($C$1:$C$1000="Collared"),ROW($C$1:$C$1000)),ROWS($1:1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Do you know why this would happen?

Thank you

 

[RobbieJannoh914]

Hey Flame,

Disregard that last post. I didnt place the array ctrl+shift+enter on the sheet reference. This would fix it. Thank you so much!!

 

[FlameRetired]

You are welcome. Glad you found the solution. Thank you for the feedback.

 

[Aladin Akyurek]

For the robustness's sake, change the bit

ROW(Sheet2!$C$1:$C$1000)

to

ROW(Sheet2!$C$1:$C$1000)-ROW(Sheet2!$C$1)+1

 

[FlameRetired]

For the robustness's sake, change the bit

ROW(Sheet2!$C$1:$C$1000)

to

ROW(Sheet2!$C$1:$C$1000)-ROW(Sheet2!$C$1)+1

Thanks Aladin. Of course I have to agree. I needed to be reminded. Appreciate it.