Andrea Bernard
asked on
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
Let me know if you have any questions! Thank you!!!!
Excel-Help.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you again!!!