Return Values in a Column if date is between 2 columns

RobbieJannoh914

New Member
Joined
Feb 16, 2017
Messages
3
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

ABCDEF
13/1/2015Collared1/1/20151/31/20153.00
2Fixed2/1/20152/28/20154.00
3Collared3/1/20153/31/20154.00
4Collared4/1/20154/30/20155.00
5Fixed5/1/20155/31/20153.00

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
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.


<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">3/1/2015</td><td style="text-align: right;;">4</td><td style=";">Collared</td><td style="text-align: right;;">1/1/2015</td><td style="text-align: right;;">1/31/2015</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";"></td><td style=";">Fixed</td><td style="text-align: right;;">2/1/2015</td><td style="text-align: right;;">2/28/2015</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";"></td><td style=";">Collared</td><td style="text-align: right;;">3/1/2015</td><td style="text-align: right;;">3/31/2015</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style=";"></td><td style=";">Collared</td><td style="text-align: right;;">4/1/2015</td><td style="text-align: right;;">4/30/2015</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";"></td><td style=";">Fixed</td><td style="text-align: right;;">5/1/2015</td><td style="text-align: right;;">5/31/2015</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$F$1:$F$1000,SMALL(<font color="Green">IF(<font color="Purple">(<font color="Teal">$A$1>=$D$1:$D$1000</font>)*(<font color="Teal">$A$1<=$E$1:$E$1000</font>)*(<font color="Teal">$C$1:$C$1000="Collared"</font>),ROW(<font color="Teal">$C$1:$C$1000</font>)</font>),ROWS(<font color="Purple">$1:1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

RobbieJannoh914

New Member
Joined
Feb 16, 2017
Messages
3
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
ABCDEF
13/1/20153
2
3
4
5

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>


Sheet2
ABCDEF
13/1/20154Collared1/1/20151/31/20153
2Fixed2/1/20152/28/20154
3Collared3/1/20153/31/20154
4Collared4/1/20154/30/20155
5Fixed5/1/20155/31/20153

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>


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

Array Formulas
CellFormula
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))),"")}

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Do you know why this would happen?

Thank you
 

RobbieJannoh914

New Member
Joined
Feb 16, 2017
Messages
3
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

Active Member
Joined
Feb 19, 2016
Messages
326
You are welcome. Glad you found the solution. Thank you for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,372
Messages
5,444,074
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top