lookup between range and return set values help?

[excelnovice1979]

Hi all,

i'm sure there is a simple way to do this but i'm not aware of it and after a quick look online i haven't found the answer yet, or if i did i didn't understand it so didn't realise it was what i needed!

so the problem:
i have a sheet where i want users to enter data, asset purchases, entering details such as date purchased, amount, description etc. Now what i want to do is have a set of reference data on another sheet, perhaps a table or named range which will have details of accounting periods such as date from & date to and then an associated reference e.g. Year End 2012 or Month 1

What i want to do is have a formula or macro or something so that when the user enters the date or purchase of the asset, another cell then looks up that date against the set of reference and which ever period it falls in it will return with the associated reference

Does that make sense?

E.G Reference Table on another sheet
[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]Date from[/TD]
[TD]Date to[/TD]
[TD]Reference[/TD]
[/TR]
[TR]
[TD]01/01/2011[/TD]
[TD]31/12/2011[/TD]
[TD]YE 2011[/TD]
[/TR]
[TR]
[TD]01/01/2012[/TD]
[TD]31/12/2012[/TD]
[TD]YE 2012[/TD]
[/TR]
</tbody>[/TABLE]






then user sheet is, and the YE ref is automatically picked up by checking purchase date between reference table dates:[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Purch Date[/TD]
[TD]YE Ref[/TD]
[TD]£[/TD]
[/TR]
[TR]
[TD]31/03/2011[/TD]
[TD]YE 2011[/TD]
[TD]£10,000[/TD]
[/TR]
[TR]
[TD]20/11/2012[/TD]
[TD]YE 2012[/TD]
[TD]£5,000[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your help!