Returning Values based on date falling between Date ranges

Attaching the sheet as I want to return the data. Please refer to the attachment as it has all required details.

I have one date and a table with date ranges, I want to return the value from the date table which falls in between the date range.
For further clarity on my problem, please see the attachment.

"Needs the value appearing in column G, if the value in A5 is (less than F5, then return G5), if its equal or more than F5 but less than F6, then return G6), if its equal or more than F6 but less than F7, then return G7 ) and so on……."

Originally Posted by MarvinP

Hi honest1122

Find the attached with a VLookup formula that should solve your problem. You will need real dates instead of pointers to a Z drive I don't have. See if this works for you.

Originally Posted by Fotis1991

Alternatively to Marvin's idea....

=INDEX($G$5:$G$11,MATCH(A5,$F$5:$F$11,1))

Thanks for your replies but both the formulas are giving me wrong values.
Say for example, I put date as 14th July 12, I need value as 4 but with both the formulas, its coming as 3.

Originally Posted by Fotis1991

Not sure for other dates but for your example, this formula, gives you what you ask for...

=INDEX($G$5:$G$11,MATCH(A5,$F$5:$F$11,1)+1)

Now its adding 1 to all the dates, for example, If I am putting date as 15th July, its coming as 5 whereas in the table, exact date is there as 15th July and the value should return as 4.


What I done is that, I have updated the sheets which all possible combinations and expected values to return to it. Please have a look.

Originally Posted by MarvinP

Hi,

On your sample worksheet you have pointers to a remote drive. Those may be TEXT instead of real Date values. Somehow you need to make them dates and not text for our formulas to work. Also - you will need to type in a date that is greater than the first date in the table for the vlookup to work. If you put in a date outside the table's range it will not give you what you want.

Right, I have removed the link for the dates. Also its fine, if the formula return as error for dates falling outside the dates in the table. Please see my post for updated sheet with expected results for various dates.

Originally Posted by MarvinP

Hi,

On your sample worksheet you have pointers to a remote drive. Those may be TEXT instead of real Date values. Somehow you need to make them dates and not text for our formulas to work. Also - you will need to type in a date that is greater than the first date in the table for the vlookup to work. If you put in a date outside the table's range it will not give you what you want.

Right, I have removed the link for the dates. Also its fine, if the formula return as error for dates falling outside the dates in the table. Please see my post for updated sheet with expected results for various dates.

Any pointers guys?