Excel 2007 : Date range nested vlookup question

[xlhelp3]

I have 2 worksheets that I need to pull a particular value from 1 by searching a date range from another.

Ex: Workbook 1

Item, Cost, Start Dt, End Dt

Ex: Workbook 2

Item, Date of purchase, Cost on DOP

I am looking to get the cost on date of purchase by using a vlookup of some sort to give the correct cost by looking looking through start date-end date. The date of purchase will fall in between the start and end. The item has multiple costs depending on the date range so I need something that will shop the ranges.

Thanks!

[daddylonglegs]

Are there multiple items listed in sheet1, each with various date ranges? If so are these all grouped together for a single item?

[xlhelp3]

There are multiple items on sheet 1 each sorted by item then by start date. There are multiple costs per item depending on start-end dates. That's where I am having problems.

[daddylonglegs]

OK assuming data in sheet1 is in rows 2 to 100 with items in A, costs in B and start dates in C you could try this formula in C2 in sheet2

=LOOKUP(B2,IF(Sheet1!A$2:A$100=A2,Sheet1!C$2:C$100),IF(Sheet1!A$2:A$100=A2,Sheet1!B$2:B$100))

confirmed with CTRL+SHIFT+ENTER and copied down

Note this assumes that you have date ranges without gaps, just looks up the start date......

[xlhelp3]

I just tried the example unfortunately it didn't take care of what I needed. There are gaps between the date ranges. Also the dates are not exact matches to the start and end. The dates may be somewhere in the middle for example

SHEET 1
Item Cost Start End
A123 10 1/1/2008 6/30/2009
A123 20 7/1/2009 12/30/2010

SHEET2
Item DOP Cost on DOP
A123 8/24/2009 ???

I need something to effectively shop between worksheets with taking the start and end into consideration.

[daddylonglegs]

....Also the dates are not exact matches to the start and end. The dates may be somewhere in the middle......

That's what I figured, the formula takes care of that

...There are gaps between the date ranges....

......but doesn't take care of that....

Does that mean you could input a date which doesn't fall into any range?

Either way this should do it for you, assuming the same setup as before

=LOOKUP(2,1/(Sheet1!A$2:A$100=A2)/(B2>=Sheet1!C$2:C$100)/(B2<=Sheet1!D$2:D$100),Sheet1!B$2:B$100)

if the date doesn't fall in any of the ranges defined for that item you'll get #N/A error

[dynamoracing]

I can see that this thread is old, but this is exactly what I need, but i need to match 3 criteria ( Item, Date, BSP ) and get Cost.

Do I just add another lookup to this formula in the beginning?

=LOOKUP(2,1/(Sheet1!A$2:A$100=A2)/(B2>=Sheet1!C$2:C$100)/(B2<=Sheet1!D$2:D$100),Sheet1!B$2:B$100)