Help with Index Match, I think.

Andrea Bernard
Andrea Bernard used Ask the Experts™
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!!!!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Finance Analyst
Assuming you want the rounded % from the table where the product and price tier match to be multiplied with the Sales value:


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.
Rob HensonFinance Analyst
Without the additional column you can use SUMIFS but still need to demerge the cells in column A:


Second version attached with this on sheet2
Rob HensonFinance Analyst
And now (the final curtain) a version without removal of the merged cells:


Updated version attached, on sheet3


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!!!
Rob HensonFinance Analyst

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial