# Index Match

#### Stclements1

##### Board Regular
I am trying to create an interest return calculator.

In Cell B1 the value of the loan is entered
In Cell B2 the term of the loan is selected from a drop-down list numbered 1-3
The problem I am having is that there are multiple variables on the interest return dependent on the size of the loan and the term of the loan.
What I am trying to return In Cell B6 is the loan rate based on the loan amount and the term
All the loan rates below are based on a one-year loan, but should the loan be for two or three years then it would attract additional interest as per the term rates

Example 1

Loan 50000 = .00825%
Term 2 Years = .00025%
Total Interest rate = 0085%

Example 2

Loan 100000 = .0095%
Term 3 Years = .005%
Total Interest rate = .01%

Loan rate
0-30000 =.007%
>30000 -75000=.00825%
>75000 =.0095%

Term
1 Year = 0%
2 Years = .00025%
3 Years = .0005%

If statements would be rather a large formula so I wondered whether an index match or Choose formula would make more sense, unless there is a simpler formula.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### jasonb75

##### Well-known Member
Something like

=LOOKUP(loan,{0,30001,75001},{0.007,0.00825,0.0095})%+CHOOSE(term,0,0.00025,0.0005)%

Change the descriptions in the formula as needed.

##### Well-known Member
Hi StClements1,

INDEX & MATCH would probably be easiest, or even a VLOOKUP.

Book2
ABCDEFGHIJKL
1Value100000Value ListPercentTerm ListPercent
2Term300.007%10.%
3300000.00825%20.00025%
4750000.0095%30.0005%
5
6Loan Rate0.01%
7
Sheet1
Cell Formulas
RangeFormula
B6B6=INDEX(\$I\$2:\$I\$4,MATCH(\$B\$1,\$H\$2:\$H\$4,1))+INDEX(\$L\$2:\$L\$4,MATCH(\$B\$2,\$K\$2:\$K\$4,1))

#### Stclements1

##### Board Regular
Thank you both for getting back to me and for your time and guidance, I went with the Index Match formula and it worked perfectly, I did try the Lookup formula but it didn't work on my sheet. Once again many thanks for your time and efforts its much appreciated.

• #### jasonb75

##### Well-known Member
I did try the Lookup formula but it didn't work on my sheet.
Works fine for me, did you change the descriptions as instructed?

Replies
3
Views
78
Replies
1
Views
73
Replies
0
Views
131
Replies
2
Views
136
Replies
11
Views
817