I'm not sure that I really understand what it is you are lookinf for but
does:

=IF(SUMPRODUCT((A2:A4=B11)*(E2:E4<=A11)*(F2:F4=A1 1)*ROW(A1:A3))=0,"No
Match!",INDEX(D2:D4,SUMPRODUCT((A2:A4=B11)*(E2:E4< =A11)*(F2:F4=A11)*ROW(A1:A3))))

Give you the right answer?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Ian_W-at-GMail" wrote in message
ups.com...
PLEASE HELP!!! REPOST:


I am trying to write a formula to lookup an item price in the price
history table (A1:E4). I want the transaction table (A9:G11) to
pickup the correct price for the item purchased based on the date of
the transaction AND the price of the item that was effective on that
date.


In the example below, Item# 1234 had a transaction take place on
3/10/07. The pricing for the item on that date was $12. If the same
item was purchased between 1/1/06 and 2/1/07, the XLS should pickup
the $5 price.


I know that I've done this formula before but I can't remember (Its
not just VLOOKUP). Any thoughts????


ITEM_NO ITEM_DESC PRICE PR_BEG_DTE PR_END_DTE
1234 Test Item1 $5.00 1/1/06
2/1/07
1234 Test Item1 $8.00 2/2/06 12/31/07
1234 Test Item1 $10.00 1/1/08 6/1/08


Transaction Date Item_No Unit Price Quantity Sub-
Total Sales Tax Sale_Total
6/15/06 1234 $5.00
1 $5.00
5/10/07 1234 $8.00
1 $5.00
3/10/07 1234 $8.00
1 $5.00



On Mar 13, 2:48 pm, "Ian_W-at-GMail" wrote:
I am trying to write aformulato lookup an itempricein
thepricehistorytable(A1:E4). I want the transactiontable(A9:G11) to
pickup thecorrectpricefor the item purchased based on the date of
the transaction AND thepriceof the item that was effective on that
date.

In the example below, Item# 1234 had a transaction take place on
3/10/07. The pricing for the item on that date was $12. If the same
item was purchased between 1/1/06 and 2/1/07, the XLS should pickup
the $5price.

I know that I've done thisformulabefore but I can't remember (Its
not just VLOOKUP). Any thoughts????

ITEM_NO ITEM_DESC PRICE PR_BEG_DTE PR_END_DTE
1234 Test Item1 $5.00 1/1/06 2/1/07
1234 Test Item1 $8.00 2/2/06
12/31/07
1234 Test Item1 $10.00 1/1/08 6/1/08

Transaction Date Item_No UnitPrice Quantity Sub-Total
Sales Tax
Sale_Total
6/15/06 1234 $5.00
1 $5.00
5/10/07 1234 $8.00
1 $5.00
3/10/07 1234 $8.00
1 $5.00