|
Help: Need Excel formula to return correct price from price history table
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 |
Help: Need Excel formula to return correct price from price history table
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 |
Help: Need Excel formula to return correct price from price history table
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 |
Help: Need Excel formula to return correct price from price history table
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 |
Help: Need Excel formula to return correct price from price history table
"Ian_W-at-GMail" wrote in message
oups.com... 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 - 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 with @tiscali.co.uk |
| All times are GMT +1. The time now is 10:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com