Multiple Lookups & Formula

twothings

Board Regular
Joined
Jul 9, 2011
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hello

I am trying to do a multiple figure lookup to calculate a loan fee. The Property Value and Loan Amount values are variables, and the Percentage is calculated as Loan / Value.

To calculate the loan fee, I need to match the Loan Amount on the horizontal axis, and the Percentage on the vertical axis to get the intersecting number (as shown in yellow). I then multiply the loan amount by the intersection number to come up with the loan fee. Could someone please assist in a formula for this. I have attempted vlookup, index and match unsuccessfully.

This link will provide you access to the excel file on my OneDrive Loan Fee.xlsx

1622925469358.png


Thank you in advance.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Index and match will be the easiest. In principle the formula would be

=INDEX(Result range,MATCH(percentage, Loan to Value Left column),MATCH(Loan amount, Loan amount ranges top row))

For a formula with the correct ranges, please post your example using XL2BB. Many forum users are unable to access workbooks on fileshare sites due to their security settings.
 
Upvote 0
Thanks for your reply. Here is the mini sheet as recommended.


Loan Fee.xlsx
ABCDEFGHIJKL
1
2LOAN AMOUNT RANGES
3$0.01$300,001$500,001$750,001$1,000,001$1,500,000$2,000,000
4$300,000$500,000$750,000$1,000,000$1,500,000$2,000,000$5,000,000
5LOAN-TO-VALUE80.01%81%0.4800.6300.7900.8200.8700.9000.920
681.01%82%0.4800.6300.8500.8500.8900.9501.000
782.01%83%0.5800.8401.0401.0701.2001.2401.290
883.01%84%0.7000.8901.1001.1301.2401.3001.340
984.01%85%0.7501.0501.2901.3801.4401.4901.560
1085.01%86%0.8401.0901.4501.4801.5201.6201.640
1186.01%87%0.8901.2301.6001.6301.7201.8201.860
1287.01%88%1.0001.3101.7101.7501.7601.8601.930
1388.01%89%1.1801.5401.9001.9302.0802.1102.150
1489.01%90%1.3001.6201.9502.0002.1502.2002.260
1590.01%91%1.7602.2803.3803.4103.7103.8504.030
1691.01%92%1.7802.3303.4403.4803.7803.9204.100
1792.01%93%1.9802.6203.6403.6703.9804.1204.350
1893.01%94%1.9902.6703.7503.8404.1904.3304.470
1994.01%95%2.2202.8803.9003.9204.3204.4304.600
20
21Example
22Loan Amount$450,000
23Property Value$500,000
24Loan to Value %90.00%
25
26Loan Fee$450,000 x 1.95% = $8,775
27
28
29
FEE
Cell Formulas
RangeFormula
F24F24=F22/F23
 
Upvote 0
Based on your mini sheet this formula will give you the percentage (1.62 for the example, not 1.95). Note that as the figures in the table are numbers instead of percentages, the result needs to be divided by 100. (1.62 as a decimal is actually 162%, not 1.62%).
Excel Formula:
=INDEX($E$5:$K$19,MATCH(F24,$C$5:$C$19),MATCH(F22,$E$3:$K$3))/100
You can then multiply that result by the loan amount to get the relevant loan fee.
 
Upvote 0
Solution
Thank you Jason, that is excellent. Yes of course it should be 1.62, I had the wrong column selected! Your assistance is very much appreciated.

Based on your mini sheet this formula will give you the percentage (1.62 for the example, not 1.95). Note that as the figures in the table are numbers instead of percentages, the result needs to be divided by 100. (1.62 as a decimal is actually 162%, not 1.62%).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top