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>

A</SPAN></SPAN>	B</SPAN></SPAN>	C</SPAN></SPAN>
1/1/2014</SPAN></SPAN>	7/1/2014</SPAN></SPAN>	3.625</SPAN></SPAN>
7/1/2013</SPAN></SPAN>	1/1/2014</SPAN></SPAN>	1.75</SPAN></SPAN>
1/1/2013</SPAN></SPAN>	7/1/2013</SPAN></SPAN>	2.5</SPAN></SPAN>
7/1/2012</SPAN></SPAN>	1/1/2013</SPAN></SPAN>	2.75</SPAN></SPAN>
1/1/2012</SPAN></SPAN>	7/1/2012</SPAN></SPAN>	8.125</SPAN></SPAN>
7/1/2011</SPAN></SPAN>	1/1/2012</SPAN></SPAN>	4.125</SPAN></SPAN>
1/1/2011</SPAN></SPAN>	7/1/2011</SPAN></SPAN>	12.5</SPAN></SPAN>
7/1/2010</SPAN></SPAN>	1/1/2011</SPAN></SPAN>	14.000</SPAN></SPAN>
1/1/2010</SPAN></SPAN>	7/1/2010</SPAN></SPAN>	4.25</SPAN></SPAN>

<TBODY>
</TBODY>

Etc etc. </SPAN></SPAN>

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

Dates</SPAN></SPAN>

6/25/1987</SPAN></SPAN>

6/20/2010</SPAN></SPAN>

2/13/2014</SPAN></SPAN>

4/15/1990</SPAN></SPAN>

6/12/1991</SPAN></SPAN>

4/24/1987</SPAN></SPAN>

1/24/2010</SPAN></SPAN>

8/24/2011</SPAN></SPAN>

<TBODY>
</TBODY>

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.