# Return Values in a Column if date is between 2 columns

#### RobbieJannoh914

##### New Member
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 see table below for example

 A B C D E F 1 3/1/2015 Collared 1/1/2015 1/31/2015 3.00 2 Fixed 2/1/2015 2/28/2015 4.00 3 Collared 3/1/2015 3/31/2015 4.00 4 Collared 4/1/2015 4/30/2015 5.00 5 Fixed 5/1/2015 5/31/2015 3.00

<tbody>
</tbody>

### 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
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>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### RobbieJannoh914

##### New Member
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

</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

</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))),"")}

</tbody>

<tbody>
</tbody>

Do you know why this would happen?

Thank you

#### RobbieJannoh914

##### New Member
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
You are welcome. Glad you found the solution. Thank you for the feedback.

##### MrExcel MVP
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

##### Active Member
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.