Help with Index Match, I think.

Hi there! I'm trying to work on a pricing project and how much a product will lift in sales based off a certain % off. Right now, as you'll see attached, I have an item, and a price, plus an ideal % off. I need to round that % down and look to an uplift table what the lift to sales would be. Does anyone know how to do this?

Let me know if you have any questions! Thank you!!!!
Excel-Help.xlsx
Andrea BernardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Assuming you want the rounded % from the table where the product and price tier match to be multiplied with the Sales value:

=ROUNDDOWN(AA2*INDEX($E$1:$R$17,MATCH($V2&"_"&W2,$A$1:$A$17,0),MATCH(Y2,$E$1:$R$1,1)),0)

I have removed the merged cells in column A and repeated the headers. I also have had to add a helper column at column A which combines the Product and price tier into one string.

See attached.
Excel-Help.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Without the additional column you can use SUMIFS but still need to demerge the cells in column A:

=ROUNDDOWN(Z2*SUMIFS(OFFSET($C$1,0,MATCH(X2,$D$1:$Q$1,1),COUNT(D:D),1),$A$1:$A$17,U2,$B$1:$B$17,V2),0)

Second version attached with this on sheet2
Excel-Help.xlsx
0
Rob HensonFinance AnalystCommented:
And now (the final curtain) a version without removal of the merged cells:

=ROUNDDOWN(Z2*INDEX($A$1:$Q$17,MATCH($U2,$A:$A,0)+MATCH($V2,$B:$B,0)-2,MATCH(X2,$D$1:$Q$1,1)+3),0)

Updated version attached, on sheet3
Excel-Help.xlsx
0
Andrea BernardAuthor Commented:
Hi Rob! Thank you so much for your help! If you have some time, can you explain to me what you did in the last solution? Totally OK if you don't! Just want to try and learn as I go along as well.

Thank you again!!!
0
Rob HensonFinance AnalystCommented:
Apologies for the delay, I was working over the weekend.

The last suggestion with no need to remove Merged Cells or helper columns only works because of the repeated/consistent layout of your data.

The syntax of INDEX has two options but in this instance is =INDEX(Range, Row, Column)

To determine Row and Column I have used the MATCH function. The Syntax of MATCH has 3 options for the type of lookup to find.

=MATCH(Lookup Value, Range, LookupType)

Lookup Type:
-1   Greater than - Finds the smallest value that is greater than or equal to look up value. Lookup range must be in descending order
0    Exact - Finds the first value that is exactly equal to lookup value. Lookup range can be in any order
1    Less than - Finds the largest value that is less than or equal to look up value. Look up range must be in ascending order.

So within the INDEX function:

Range - A1:Q17, the whole of your table

Row - because of the way your data is consistently laid out the row can be easily calculated. The first MATCH uses an exact match (lookup type 0) to find the product class in column A to find the start of that product group. The second MATCH also uses an exact match to find the Price Tier in column B. As the product groups have all the same price tiers the location of the tier within the group is consistent so the first occurrence (in the first group) can be used as the position. By adding the two MATCH numbers together you get the row for the Price Tier for that product (less 2 because we are looking at whole columns so need to allow for the headers).

Column - The column uses a "Less than" match to find the discount percentage. The row of available percentages is shown in ascending order (after converting to number rather than text) so using the MATCH with "Less than" will stop at the value just before your specified rate. To allow for the fact that the rates start in column D I have started the Lookup range in column D but have then had to add 3 to allow for columns A to C being used in the INDEX range. Looking at it this can actually be amended to match the rate in columns A to Q and then not add 3; I had assumed that the text values in A1 to C1 would confuse the MATCH but it would appear not.

Once the uplift rate has been found I have then used it to multiply with the previous sales figure. As you can't sell a part of a product I have used the ROUNDDOWN to round down to an integer.

Hope that all makes sense but feel free to ask further questions if required.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sales

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.