I was hoping that someone can help me in writing array formulas in excel that would return pricing from "Last Ordered" and the "Date of price changed" from a list of invoices.
Below would be the example of the information that I need from the data table. Please help.
Item#: 201 (entered value)
Last Ordered: 4/24/18 (formula, the latest invoice that includes the item#, this is where I need the help)
Current Price: $7.00 (formula, pricing from latest invoice, which can be a simple vlookup from "Last Order")
Date of Price Change : 8/28/17 (formula, the most recent invoice that is <> the Current Price, this is where I need the help)
Previous Price: $6.50 (formula, which can be a simple vlookup from "Date of Price Change")
In this example the "Date of Price Change" formula would skip over 11/20/17 because the pricing is still $7.00. The pricing is not <> the current price. And would instead return 8/28/17 because that price < current price.
Thanks again for any help
Data
<tbody>
</tbody>
Below would be the example of the information that I need from the data table. Please help.
Item#: 201 (entered value)
Last Ordered: 4/24/18 (formula, the latest invoice that includes the item#, this is where I need the help)
Current Price: $7.00 (formula, pricing from latest invoice, which can be a simple vlookup from "Last Order")
Date of Price Change : 8/28/17 (formula, the most recent invoice that is <> the Current Price, this is where I need the help)
Previous Price: $6.50 (formula, which can be a simple vlookup from "Date of Price Change")
In this example the "Date of Price Change" formula would skip over 11/20/17 because the pricing is still $7.00. The pricing is not <> the current price. And would instead return 8/28/17 because that price < current price.
Thanks again for any help
Data
Column A | Column B | Column C |
Date | Item # | Price |
5/11/18 | 324 | $7.00 |
4/24/18 | 201 | $7.00 |
1/1/18 | 324 | $6.00 |
11/20/17 | 201 | $7.00 |
10/9/17 | 324 | $6.00 |
10/9/17 | 324 | $6.00 |
8/28/17 | 201 | $6.50 |
7/17/17 | 201 | $6.50 |
6/5/17 | 324 | $5.50 |
4/24/17 | 201 | $6.00 |
3/13/17 | 324 | $5.50 |
1/30/17 | 201 | 6.50 |
<tbody>
</tbody>