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

#### Aubin

##### New Member
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

#### Attachments

• 10.3 KB Views: 2

### 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
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
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
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
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
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
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
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
Glad we could help & thanks for the feedback