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>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
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
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
You are welcome. Glad you found the solution. Thank you for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top