Return a value (c) if this date is between (a) and (b)

[jmcn8422]

I have a matrix of date ranges with a value on sheet 1, like this:</SPAN></SPAN>
[TABLE="width: 344"]
<TBODY>[TR]
[TD]A</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1/1/2014</SPAN></SPAN>
[/TD]
[TD]7/1/2014</SPAN></SPAN>
[/TD]
[TD]3.625</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]7/1/2013</SPAN></SPAN>
[/TD]
[TD]1/1/2014</SPAN></SPAN>
[/TD]
[TD]1.75</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1/1/2013</SPAN></SPAN>
[/TD]
[TD]7/1/2013</SPAN></SPAN>
[/TD]
[TD]2.5</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]7/1/2012</SPAN></SPAN>
[/TD]
[TD]1/1/2013</SPAN></SPAN>
[/TD]
[TD]2.75</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1/1/2012</SPAN></SPAN>
[/TD]
[TD]7/1/2012</SPAN></SPAN>
[/TD]
[TD]8.125</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]7/1/2011</SPAN></SPAN>
[/TD]
[TD]1/1/2012</SPAN></SPAN>
[/TD]
[TD]4.125</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1/1/2011</SPAN></SPAN>
[/TD]
[TD]7/1/2011</SPAN></SPAN>
[/TD]
[TD]12.5</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]7/1/2010</SPAN></SPAN>
[/TD]
[TD]1/1/2011</SPAN></SPAN>
[/TD]
[TD]14.000</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1/1/2010</SPAN></SPAN>
[/TD]
[TD]7/1/2010</SPAN></SPAN>
[/TD]
[TD]4.25</SPAN></SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
Etc etc. </SPAN></SPAN>

On a Sheet 2 I have a list of dates</SPAN></SPAN>

[TABLE="width: 109"]
<TBODY>[TR]
[TD]Dates</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]6/25/1987</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]6/20/2010</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]2/13/2014</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]4/15/1990</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]6/12/1991</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]4/24/1987</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1/24/2010</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]8/24/2011</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[/TR]
</TBODY>[/TABLE]
I am looking for a formula that will identify and return the value in column C if the date in question is in between column A and B.</SPAN></SPAN>
Is this possible? </SPAN></SPAN>

 

[Jonmo1]

Your date ranges overlap..

7/1/2013, should that return 1.75 or 2.5 ?
1/1/2013, should that return 2.5 or 2.75 ?

Also, can you sort the dates ASCENDING by column A (oldest dates on top) ?

 

[jmcn8422]

7/1/2013 should return 1.75
1/1/2013 should return 2.5


yes, I can sort acending, if need be i can modify the matrix to not overlap, if that makes this easier.

 

[Jonmo1]

If the dates are sorted Ascending by column A, and given your clarification of the overlaps...

try this assuming both sheets have the dates beginning in A2.
=LOOKUP('Sheet 2'!A2,'Sheet 1'!A$2:C$10)


FYI, column B (the high end of each date range) is not required.

 

[jmcn8422]

The vlookup you have included does not show a column index, or lookup range, am I missing something?

I believe this may be more complex than a Vlookup, as the search data is not an exact match,
perhaps my phrasing is off, I am looking for something that will do something like this

If x is between A and B return C

 

[Jonmo1]

The vlookup you have included does not show a column index, or lookup range, am I missing something?

Yes, it's not a Vlookup

 

[jmcn8422]

WORKS PERFECTLY! Thank you very much! !! !! !!!!!

 

[Jonmo1]

Glad to help, thanks for the feedback.