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

[Aubin]

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.

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

 

[DanteAmor]

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]

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
	A	B	C	D	E	F	G	H	I
1						10%	20%	30%	40%
2	Product	A			A	20	30	40	50
3	Price	29.5			B	22	32	42	52
4	Result	10%			C	24	34	44	54
5					D	26	36	46	56
6	Product	B
7	Price	32.5
8	Result	20%
Hoja7

Cell Formulas
Range	Formula
B4,B8	B4	=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]

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]

Another option:

Book2
	A	B	C	D	E	F	G	H	I
1						0.1	0.2	0.3	0.4
2	Product	A			A	20	30	40	50
3	Price	29.5			B	22	32	42	52
4	Result	10%			C	24	34	44	54
5					D	26	36	46	56
6	Product	B
7	Price	32.5
8	Result	20%
Sheet4

Cell Formulas
Range	Formula
B4,B8	B4	=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]

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]

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]

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]

Glad we could help & thanks for the feedback