Last Sales Price

[rajivsoni]

Hey guys,
I'm stuck with a report i need to make and need an urgent response
i have a database file containing the following:

Date | Order Number | Product Id | Product Description | Customer ID | Customer Name | Volumes sold | Selling Price | Total Value (Volume x sp)

now what i want is a formula to get the Last selling Price of a Product.

For Eg.
Lets say the Customer is "Rajiv" and the Product Purchased is a "Pendant"

I need to know @ what price was the pendant last sold to the customer rajiv.
Now the tricky part is tht this customer could have purchased the pendants before number of times. i need to know @ what price was it last purchased.
I have a date column in my database which im sure would be helpful.

Lemme know if i wasnt clear.

Thanks in Advance

 

[rajivsoni]

Yeap the Price of the product for an "X" customer on the last entry date
I'm glad u helped me with it... which is why i cud figure out otherwise i wud've been going mad and crazy with it lol

 

[Aladin Akyurek]

hey thanks Aladin, this works but i have a question here, what this does is it pickes up the last row for that customer. can there be a way to pickup the Price range based on the order date ??? it should pick up the Latest Order date
Thank you soo much ..!

Control+shift+enter, not just enter:

F2: Rajiv
G2: Pendant

H2:

Control+shift+enter, not just enter:

=INDEX(PriceRange,MATCH(MAX(IF(CustomerRange=F2,
   IF(ProductRange=G2,DateRange))),IF(CustomerRange=F2,
    IF(ProductRange=G2,DateRange)),0))

 

[rajivsoni]

hey thanks a ton Aladin... The Index formula works perfect