Lookup with exact match and date range

Hi Experts!

I'm trying to do a lookup with multiple criteria, but instead of trying to find 2 exact values, one is a specific code (exact match) and the other is a date to be matched within a date range in 2 different columns.

I'm including an excel file with an example of the 2 sets of data: A) with the code, specific date, and B) with the code, date range and price to be returned into the first chart.

Hope the file helps to understand my question better. I have been trying to do it with INDEX and MATCH formulas but I have not even come close to a solution yet.

Set A
Item Code Date Desired result (currently empty )
12345678 2/15/2009 $10
12345678 7/2/2009 $9
87654321 11/12/2009 $12
87654321 1/1/2010 Not found (N/A)
87654321 5/1/2009 $19
87654321 8/3/2009 $21
87654321 10/1/2009 $23
87654321 9/30/2009 $21
12348765 2/1/2009 $5
12348765 11/12/2009 $3
12348765 10/1/2009 $3
12348765 10/1/2009 $3

Set B

Item Code Valid From Valid To Price
12345678 1/1/2009 3/1/2009 $10
12345678 4/1/2009 6/30/2009 $11
12345678 7/1/2009 9/30/2009 $9
12345678 10/1/2009 12/31/2009 $12
87654321 1/1/2009 3/1/2009 $20
87654321 4/1/2009 6/30/2009 $19
87654321 7/1/2009 9/30/2009 $21
87654321 10/1/2009 12/31/2009 $23
12348765 1/1/2009 3/1/2009 $5
12348765 4/1/2009 6/30/2009 $4
12348765 7/1/2009 9/30/2009 $6
12348765 10/1/2009 12/31/2009 $3

Thanks in advance!!

You can use this array* formula in cell C2 of Sheet1:

=IFERROR(INDEX(Sheet2!$D$2:$D$13,MATCH(1,(Sheet2!$A$2:$A$13=A2)*(Sheet2!$B$2:$B$13<=B2)*(Sheet2!$C$2:$C$13>=B2),0)),"Not found")

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

then copy down as required.

Hope this helps.

Pete

That worked perfectly!! Thank you very much!!

You're welcome - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete