Help: Need Excel formula to return correct price from price history table

I

Ian_W-at-GMail

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
 
I

Ian_W-at-GMail

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
 
S

Sandy Mann

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>=A11)*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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
I

Ian_W-at-GMail

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>=A11)*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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk












- Show quoted text -
Click to expand...

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
 
S

Sandy Mann

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


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>=A11)*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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk












- Show quoted text -
Click to expand...

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
 
I

Ian_W-at-GMail

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 theformulaI gave you. I then realised that I should have made the
ranges in theformulaabsolute so try theformula:

=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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk






Hi Sandy;

Theformulayou provided is not what I am looking for.

What I am trying to do is pickup thecorrectitempriceto use from
the ItemPriceHistorytablebased on the transaction date. So, in
the example Item# 1234 Purchased on Trans Dte 6/15/06 should pickup
the UnitPriceof $5.00 based on thePriceHistoryTable(PR_Beg_DTE
<= Trans_Date <=PriceEnd Dte)

Item# 1234 Purchased on Trans Dte 5/10/07 should pickup the UnitPrice
of $8.00 based on thePriceHistoryTable(PR_Beg_DTE <= Trans_Date <=PriceEnd Dte)

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

Thank You;

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

- Show quoted text -
Click to expand...

Hi Sandy;

Your formula works!!!! I just tried it in my spreadsheet and I get
the correct values (Unit Price) returned based on the transaction
date. And you were correct, I had a type-O in the date field (s/b
2/2/07).

Thank you soooo much for your help!!! I will have to take some time
to figure out how your nested formula works....

- Ian -
 
S

Sandy Mann

Ian_W-at-GMail said:
Hi Sandy;

Your formula works!!!! I just tried it in my spreadsheet and I get
the correct values (Unit Price) returned based on the transaction
date. And you were correct, I had a type-O in the date field (s/b
2/2/07).
Click to expand...
Thank you soooo much for your help!!! I will have to take some time
to figure out how your nested formula works....
Click to expand...
- Ian -
Click to expand...


Hi Ian,

Thanks for the feedback. I will let you figure out how it works yourself
because I find that that is where I learn the most - figuring out how the
formulas of the experts around here work - I only wish that I could remember
them when I need it again!

--

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Help - Not sure which one I need! SUMIF I think?! 6
formula to look at current date and markdown date to give price 4
How do I change the following formula to allow for leap year 3
Adjusted Closing Price formula 2
SUMIF Formula Help 8
Need help to freeze or override a formula 1
Using Index Match etc. for price list 4
Formula in excel 1

Top