Is Index Match my answer here?

[sitewolf]

OK, so I've got a table similar to below and I'm looking to create a formula where if a dollar amount is input into a cell and a number 1-6 is input into another cell, an amount is multiplied by the correct corresponding percentage. In other words, $1,000 and 1 should multiply by 8%, $5,000 and 6 multiplies by 56%.

This table would be for display purposes on a form. I'd create a data table off to the side. But so far the match/lookup formulas I've created aren't quite right across the board yet.

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64"]%[/TD]
[/TR]
[TR]
[TD="class: xl63"] $0 - $1,012 [/TD]
[TD="class: xl64"] $0 - $1,372 [/TD]
[TD="class: xl64"] $0 - $1,732 [/TD]
[TD="class: xl64"] $0 - $2,092 [/TD]
[TD="class: xl64"] $0 - $2,452 [/TD]
[TD="class: xl64"] $0 - 2,812 [/TD]
[TD="class: xl65"]8%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 1,013 - 1,163 [/TD]
[TD="class: xl64"] 1,373 - 1,578 [/TD]
[TD="class: xl64"] 1,733 - 1,992 [/TD]
[TD="class: xl64"] 2,093 - 2,406 [/TD]
[TD="class: xl64"] 2,453 - 2,820 [/TD]
[TD="class: xl64"] 2,813 - 3,234 [/TD]
[TD="class: xl65"]16%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 1,164 - 1,315 [/TD]
[TD="class: xl64"] 1,579 - 1,784 [/TD]
[TD="class: xl64"] 1,993 - 2,252 [/TD]
[TD="class: xl64"] 2,407 - 2,720 [/TD]
[TD="class: xl64"] 2,821 - 3,188 [/TD]
[TD="class: xl64"] 3,235 - 3,656 [/TD]
[TD="class: xl65"]24%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 1,316 - 1,467 [/TD]
[TD="class: xl64"] 1,785 - 1,989 [/TD]
[TD="class: xl64"] 2,253 - 2,511 [/TD]
[TD="class: xl64"] 2,721 - 3,033 [/TD]
[TD="class: xl64"] 3,189 - 3,555 [/TD]
[TD="class: xl64"] 3,657 - 4,077 [/TD]
[TD="class: xl65"]32%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 1,468 - 1,619 [/TD]
[TD="class: xl64"] 1,990 - 2,195 [/TD]
[TD="class: xl64"] 2,512 - 2,771 [/TD]
[TD="class: xl64"] 3,034 - 3,347 [/TD]
[TD="class: xl64"] 3,556 - 3,923 [/TD]
[TD="class: xl64"] 4,078 - 4,499 [/TD]
[TD="class: xl65"]40%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 1,620 - 1,770 [/TD]
[TD="class: xl64"] 2,196 - 2,401 [/TD]
[TD="class: xl64"] 2,772 - 3,031 [/TD]
[TD="class: xl64"] 3,348 - 3,661 [/TD]
[TD="class: xl64"] 3,924 - 4,291 [/TD]
[TD="class: xl64"] 4,500 - 4,921 [/TD]
[TD="class: xl65"]48%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 1,771 - 1,922 [/TD]
[TD="class: xl64"] 2,402 - 2,607 [/TD]
[TD="class: xl64"] 3,032- 3,291 [/TD]
[TD="class: xl64"] 3,662 - 3,975 [/TD]
[TD="class: xl64"] 4,292 - 4,659 [/TD]
[TD="class: xl64"] 4,922 - 5,343 [/TD]
[TD="class: xl65"]56%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 1,923 - 2,074 [/TD]
[TD="class: xl64"] 2,608 - 2,813 [/TD]
[TD="class: xl64"] 3,292 - 3,551 [/TD]
[TD="class: xl64"] 3,976 - 4,289 [/TD]
[TD="class: xl64"] 4,660 - 5,027 [/TD]
[TD="class: xl64"] 5,344 - 5,765 [/TD]
[TD="class: xl65"]64%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 2,075 - 2,226 [/TD]
[TD="class: xl64"] 2,814 - 3,018 [/TD]
[TD="class: xl64"] 3,552 - 3,810 [/TD]
[TD="class: xl64"] 4,290 - 4,602 [/TD]
[TD="class: xl64"] 5,028 - 5,394 [/TD]
[TD="class: xl64"] 5,766 - 6,186 [/TD]
[TD="class: xl65"]72%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 2,227 - 2,377 [/TD]
[TD="class: xl64"] 3,019 - 3,224 [/TD]
[TD="class: xl64"] 3,811 - 4,070 [/TD]
[TD="class: xl64"] 4,603 - 4,916 [/TD]
[TD="class: xl64"] 5,395 - 5,762 [/TD]
[TD="class: xl64"] 6,187 - 6,608 [/TD]
[TD="class: xl65"]80%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 2,378 - 2,529 [/TD]
[TD="class: xl64"] 3,225 - 3,430 [/TD]
[TD="class: xl64"] 4,071 - 4,330 [/TD]
[TD="class: xl64"] 4,917 - 5,230 [/TD]
[TD="class: xl64"] 5,763 - 6,130 [/TD]
[TD="class: xl64"] 6,609 - 7,030 [/TD]
[TD="class: xl65"]88%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 2,530 - 2,681 [/TD]
[TD="class: xl64"] 3,431 - 3,636 [/TD]
[TD="class: xl64"] 4,331 - 4,590 [/TD]
[TD="class: xl64"] 5,231 - 5,544 [/TD]
[TD="class: xl64"] 6,131 - 6,498 [/TD]
[TD="class: xl64"] 7,031 - 7,452 [/TD]
[TD="class: xl65"]96%[/TD]
[/TR]
[TR]
[TD="class: xl66"] 2,682 + [/TD]
[TD="class: xl64"] 3,637 + [/TD]
[TD="class: xl64"] 4,591+ [/TD]
[TD="class: xl64"] 5,545 + [/TD]
[TD="class: xl64"] 6,499+ [/TD]
[TD="class: xl64"] 7,453+ [/TD]
[TD="class: xl65"]100%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

I2 = 5000
J2 = 6

In K2 enter:

=LOOKUP(I2,SUBSTITUTE(TRIM(LEFT(INDEX($A$2:$F$14,0,J2),FIND("-",INDEX($A$2:$F$14,0,J2)&"-")-1)),"+","")+0,$G$2:$G$14)*I2

 

[Marcelo Branco]

It would be much easier with a table where each cell contained only the lower bound of each range.

Something like

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td]

%​

[/td][td][/td][td]

Value​

[/td][td]

Column​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

8%​

[/td][td][/td][td]

1000​

[/td][td]

1​

[/td][td]

8%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1013​

[/td][td]

1373​

[/td][td]

1733​

[/td][td]

2093​

[/td][td]

2453​

[/td][td]

2813​

[/td][td]

16%​

[/td][td][/td][td]

5000​

[/td][td]

6​

[/td][td]

56%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1164​

[/td][td]

1579​

[/td][td]

1993​

[/td][td]

2407​

[/td][td]

2821​

[/td][td]

3235​

[/td][td]

24%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

1316​

[/td][td]

1785​

[/td][td]

2253​

[/td][td]

2721​

[/td][td]

3189​

[/td][td]

3657​

[/td][td]

32%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

1468​

[/td][td]

1990​

[/td][td]

2512​

[/td][td]

3034​

[/td][td]

3556​

[/td][td]

4078​

[/td][td]

40%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

1620​

[/td][td]

2196​

[/td][td]

2772​

[/td][td]

3348​

[/td][td]

3924​

[/td][td]

4500​

[/td][td]

48%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

1771​

[/td][td]

2402​

[/td][td]

3032​

[/td][td]

3662​

[/td][td]

4292​

[/td][td]

4922​

[/td][td]

56%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

1923​

[/td][td]

2608​

[/td][td]

3292​

[/td][td]

3976​

[/td][td]

4660​

[/td][td]

5344​

[/td][td]

64%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

2075​

[/td][td]

2814​

[/td][td]

3552​

[/td][td]

4290​

[/td][td]

5028​

[/td][td]

5766​

[/td][td]

72%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

2227​

[/td][td]

3019​

[/td][td]

3811​

[/td][td]

4603​

[/td][td]

5395​

[/td][td]

6187​

[/td][td]

80%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

2378​

[/td][td]

3225​

[/td][td]

4071​

[/td][td]

4917​

[/td][td]

5763​

[/td][td]

6609​

[/td][td]

88%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

2530​

[/td][td]

3431​

[/td][td]

4331​

[/td][td]

5231​

[/td][td]

6131​

[/td][td]

7031​

[/td][td]

96%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

2682​

[/td][td]

3637​

[/td][td]

4591​

[/td][td]

5545​

[/td][td]

6499​

[/td][td]

7453​

[/td][td]

100%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in K2 copied down
=LOOKUP(I2,INDEX($A$2:$F$14,0,J2),G$2:G$14)

M.

 

[sitewolf]

Thank you both, a big help!