Formula required for work - Lookup Matrix to provide result in cell

Aubin

New Member
Joined
Feb 18, 2020
Messages
3
Office Version
2016
Platform
Windows
Hi All,

I have this assignment at work, and i tried bunch of formulas but cannot get this (hopefully) easy formula right, i have tried INDEX, MATCH, COLUMN, COUNTIFS, SUMPRODUCT but all to no avail; i cannot comprehend the complexity and turn it into simple logic.

In the image attached, i have a table with product and prices, the idea is to develop a formula in Results cell (gray) that "lookup" the table for price (range) and returns the % on the 1st row of the table.

Excel 1.PNG


For Example:
1. If user selects product A then between 10-19.9 the result should give 10%
2. If user selects product B then between 32-42 the result should give 20%
3. If user selects product C then between 34-44 the result should give 30%

Can you give me an example of a formula i can use? If i can get the colour it would help, if not its OK too as i can use conditional formatting based on the %.
Not VBA just plain old excel formulas.

Thanks a ton.
Aubin
 

Attachments

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,209
Office Version
2007
Platform
Windows
Hi and welcome to MrExcel!

If user selects product A then between 10-19.9 the result should give 10%
I think your examples are not correct. For product A
In the previous text you mention 10-19.9 = 10%
But in your image you have 29.5 = 10%
And what result do you want if the price is 5?

_______________________________________________________________________
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,209
Office Version
2007
Platform
Windows
Check if the following is what you need:
For product A
0-19.9 = 0
20-29.9 = 10
30-39.9 = 20
40-49-.9 = 30
50-or more = 40

Is an Array formula:
Dante Amor.xlsm
ABCDEFGHI
110%20%30%40%
2ProductAA20304050
3Price29.5B22324252
4Result10%C24344454
5D26364656
6ProductB
7Price32.5
8Result20%
Hoja7
Cell Formulas
RangeFormula
B4,B8B4=IFERROR(INDEX($F$1:$I$1,0,MAX(($E$2:$E$5=B2)*($F$2:$I$5<=B3)*(COLUMN($F$1:$I$1)))-COLUMN($E$1)),0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,209
Office Version
2007
Platform
Windows
Other news, you can make it regular formula with sumproduct:

=IFERROR(INDEX($F$1:$I$1,0,SUMPRODUCT(MAX(($E$2:$E$5=B2)*($F$2:$I$5<=B3)*(COLUMN($F$1:$I$1))))-COLUMN($E$1)),0)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,302
Another option:

Book2
ABCDEFGHI
10.10.20.30.4
2ProductAA20304050
3Price29.5B22324252
4Result10%C24344454
5D26364656
6ProductB
7Price32.5
8Result20%
Sheet4
Cell Formulas
RangeFormula
B4,B8B4=IFERROR(LOOKUP(B3,INDEX($F$2:$I$5,MATCH(B2,$E$2:$E$5,0),0),$F$1:$I$1),0)


And yes, you'd need Conditional Formatting to get the color.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,961
If "A" is in A2 and 10% is in B1 and your product code is in Z1, and your price is in Z2, try the formula


=LOOKUP(Z2, INDEX($B$2:$E$4, MATCH(Z1,$A$2:$A$4),0), $B$1:$E$1)
 

Aubin

New Member
Joined
Feb 18, 2020
Messages
3
Office Version
2016
Platform
Windows
Thanks all, this made the calculation quite easy.
In the end used
=IFERROR(LOOKUP(B3,INDEX($F$2:$I$5,MATCH(B2,$E$2:$E$5,0),0),$F$1:$I$1),0)

I appreciate the support, i can work more productively now :)

BR
Aubin
 

Aubin

New Member
Joined
Feb 18, 2020
Messages
3
Office Version
2016
Platform
Windows
Hi and welcome to MrExcel!


I think your examples are not correct. For product A
In the previous text you mention 10-19.9 = 10%
But in your image you have 29.5 = 10%
And what result do you want if the price is 5?

_______________________________________________________________________
You are correct, that was a typo on my part. Thanks for support to understand with another example and help out :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,209
Office Version
2007
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,089,642
Messages
5,409,494
Members
403,266
Latest member
HMR120

This Week's Hot Topics

Top