# Thread: Calculation using table and two variable

1. ## Calculation using table and two variable

Ok, I hope I can explain this correctly...

I am trying to create a formula that populates a cell from a table. The table as below:
 LVR/Loan value \$300k-\$500k \$501k-\$1,000,000 81%-85% 0.5% 0.6% 86%-90% 0.7% 0.7% 91%-95% 0.8% 0.8%

The outcome that I need is for the cell to calculate the following:
LMI = Loan value x (The percentage from the table using the LVR and total lending amount).

 Current Debt LVR Loan value LMI \$500,000 90% \$400,000 XXX

Alex

2. If you list the headers in your first table to show only the lower bounds:

e.g.

 LVR/Loan value \$300,000 \$501,000 81% 0.5% 0.6% 86% 0.7% 0.7% 91% 0.8% 0.8%

Then you can use a formula like:

=I2*INDEX(\$B\$2:\$C\$4,MATCH(H2,\$A\$2:\$A\$4),MATCH(I2,\$B\$1:\$C\$1))

where your lower table is contatined in G1:J2, making I2 the first Loan Value, and H2 containing the LVR to look up.

3. You are truly an excel master. Thank you very much. It has worked perfectly!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•