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.

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

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	1​	2​	3​	4​	5​	6​	%​		Value​	Column​	Result​
2​	0​	0​	0​	0​	0​	0​	8%​		1000​	1​	8%​
3​	1013​	1373​	1733​	2093​	2453​	2813​	16%​		5000​	6​	56%​
4​	1164​	1579​	1993​	2407​	2821​	3235​	24%​
5​	1316​	1785​	2253​	2721​	3189​	3657​	32%​
6​	1468​	1990​	2512​	3034​	3556​	4078​	40%​
7​	1620​	2196​	2772​	3348​	3924​	4500​	48%​
8​	1771​	2402​	3032​	3662​	4292​	4922​	56%​
9​	1923​	2608​	3292​	3976​	4660​	5344​	64%​
10​	2075​	2814​	3552​	4290​	5028​	5766​	72%​
11​	2227​	3019​	3811​	4603​	5395​	6187​	80%​
12​	2378​	3225​	4071​	4917​	5763​	6609​	88%​
13​	2530​	3431​	4331​	5231​	6131​	7031​	96%​
14​	2682​	3637​	4591​	5545​	6499​	7453​	100%​
15​

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!