Hi Ian,

I assumed that 2/2/06 was a type for 2/2/07:

PR_BEG_DTE PR_END_DTE
1/1/06 2/1/07
2/2/06 12/31/07

When I corrected that date I got $5 returned for the 1st Transaction date
using the formula I gave you. I then realised that I should have made the
ranges in the formula absolute so try the formula:

=IF(SUMPRODUCT(($A$2:$A$4=B11)*(D2:D4<=A11)*($E$2: $E$4=A11)*ROW($A$1:$A$3))=0,"No
Match!",INDEX($C$2:$C$4,SUMPRODUCT(($A$2:$A$4=B11) *($D$2:$D$4<=A11)*($E$2:$E$4=A11)*ROW($A$1:$A$3)) ))

and copy down. I then got $8 & $10 for the other two Transaction dates.
--
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
oups.com...
On Mar 17, 2:56 pm, "Sandy Mann" wrote:
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...



PLEASEHELP!!! REPOST:

I am trying to write aformulato lookup an itempricein theprice
historytable(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

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- Hide quoted text -

- Show quoted text -

Hi Sandy;

The formula you provided is not what I am looking for.

What I am trying to do is pickup the correct item price to use from
the Item Price History table based on the transaction date. So, in
the example Item# 1234 Purchased on Trans Dte 6/15/06 should pickup
the Unit Price of $5.00 based on the Price History Table (PR_Beg_DTE
<= Trans_Date <= Price End Dte)

Item# 1234 Purchased on Trans Dte 5/10/07 should pickup the Unit Price
of $8.00 based on the Price History Table (PR_Beg_DTE <= Trans_Date <=
Price End Dte)


This is what I am trying to do across many item#'s. Any thoughts???

Thank You;


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 $??? (s/b$5) 1
$5.00
5/10/07 1234 $?? (s/b $8) 1
$8.00
3/10/08 1234 $??? (s/b$10) 1
$10.00