Hi,
I am working on the project and I am stuck with the vlookup that always pick up the first value of the array.
Here is example of my work:
Sheet 1
Product Customer Selling Date Market Price Concatenate
orange ABC 1/10/2011 5 orange ABC
apple ABC 1/15/2011 8 apple ABC
orange ABC 1/14/2011 5 orange ABC
apple XYZ 1/18/2011 7 apple XYZ
Sheet 2
Concate Promotion# Product Customer Start date End Date Discount$
orange ABC 1001 orange ABC 1/14/2011 1/18/2011 $2
orange ABC 1002 orange ABC 1/9/2011 1/12/2011 $1
apple ABC 1003 apple ABC 1/13/2011 1/16/2011 $2
apple XYZ 1004 apple XYZ 1/17/2011 1/25/2011 $2
Remark : I must sort by Discount$ (decending) to give the best discount amount to the customer.
What I want is, on Sheet 1, I need to see which promotion works best for each selling transaction. Customer will get the discount if they sell the product within the promotion date range.
If I only use the basic VLOOKUP, here is what I get:
Product Customer Selling Date Market Price Concatenate Promotion#
orange ABC 1/10/2011 5 orange ABC 1001
apple ABC 1/15/2011 8 apple ABC 1003
orange ABC 1/14/2011 5 orange ABC 1001
apple XYZ 1/18/2011 7 apple XYZ 1004
You will see that the first row turns the value to "Promotion 1001" which the selling date is not within Promotion 1001's date range.
How can I get the correct result which it has to be the second occurance (Promotion 1002)? without using Access or VBA.
Please help.
Thank you so much.
I am working on the project and I am stuck with the vlookup that always pick up the first value of the array.
Here is example of my work:
Sheet 1
Product Customer Selling Date Market Price Concatenate
orange ABC 1/10/2011 5 orange ABC
apple ABC 1/15/2011 8 apple ABC
orange ABC 1/14/2011 5 orange ABC
apple XYZ 1/18/2011 7 apple XYZ
Sheet 2
Concate Promotion# Product Customer Start date End Date Discount$
orange ABC 1001 orange ABC 1/14/2011 1/18/2011 $2
orange ABC 1002 orange ABC 1/9/2011 1/12/2011 $1
apple ABC 1003 apple ABC 1/13/2011 1/16/2011 $2
apple XYZ 1004 apple XYZ 1/17/2011 1/25/2011 $2
Remark : I must sort by Discount$ (decending) to give the best discount amount to the customer.
What I want is, on Sheet 1, I need to see which promotion works best for each selling transaction. Customer will get the discount if they sell the product within the promotion date range.
If I only use the basic VLOOKUP, here is what I get:
Product Customer Selling Date Market Price Concatenate Promotion#
orange ABC 1/10/2011 5 orange ABC 1001
apple ABC 1/15/2011 8 apple ABC 1003
orange ABC 1/14/2011 5 orange ABC 1001
apple XYZ 1/18/2011 7 apple XYZ 1004
You will see that the first row turns the value to "Promotion 1001" which the selling date is not within Promotion 1001's date range.
How can I get the correct result which it has to be the second occurance (Promotion 1002)? without using Access or VBA.
Please help.
Thank you so much.
