# Last Sales Price

#### rajivsoni

##### Board Regular
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.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
??????????????????????????????

help me.....!!

Control+shift+enter, not just enter:
Rich (BB code):
``````=LOOKUP(9.99999999999999E+307,IF(CustomerRange="Rajiv",
IF(ProductRange="Pendant",1)),PriceRange)``````

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 ..!

Perhaps something like this.....
With your data structured like your first post and positioned in Col_A through CoL_I
and
K1: a customer....eg Rajiv
L1: a product......eg Pendant
M1: a date..........eg 2011-Jan-07

This regular formula returns the last price for that customer for that item on that date (I employed Aladin's range name convention):
Code:
``````N1: =LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)
*(DateRange=M1)),PriceRange)``````

Note each of those ranges begins in the first row under the headings (probably Row_2)

Is that something you can work with?

Ron,
DateRange=M1
I'm not specifying any date in M1.
The formula needs to pick the Latest date from the DateRange

Ron,
DateRange=M1
I'm not specifying any date in M1.
The formula needs to pick the Latest date from the DateRange
If you want regular formulas to return
• the last price for the product for the customer
Code:
``=LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)),PriceRange)``
• the date for that price
Code:
``=LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)),DateRange)``

Does that help?...or do you need something else?

hey Ron,
Tht dint really help but thanks anyway i tried cracking my brains to come up with this one:
=LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)
*(DateRange=MAX(DateRange))),PriceRange)
Thanks a ton

OK...I understand. You wanted to only search for the customer and product on the last entry date. I'm glad you figured it out. Thanks for letting us know your solution.

Replies
4
Views
401
Replies
27
Views
1K
Replies
9
Views
266
Replies
5
Views
185
Replies
1
Views
275

1,218,560
Messages
6,143,206
Members
450,469
Latest member
brent3162

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back