Multiple Lookups & Formula

[twothings]

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

Thank you in advance.

 

[jasonb75]

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.

 

[twothings]

Thanks for your reply. Here is the mini sheet as recommended.

Loan Fee.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2					LOAN 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
5		LOAN-TO-VALUE	80.01%	81%	0.480	0.630	0.790	0.820	0.870	0.900	0.920
6			81.01%	82%	0.480	0.630	0.850	0.850	0.890	0.950	1.000
7			82.01%	83%	0.580	0.840	1.040	1.070	1.200	1.240	1.290
8			83.01%	84%	0.700	0.890	1.100	1.130	1.240	1.300	1.340
9			84.01%	85%	0.750	1.050	1.290	1.380	1.440	1.490	1.560
10			85.01%	86%	0.840	1.090	1.450	1.480	1.520	1.620	1.640
11			86.01%	87%	0.890	1.230	1.600	1.630	1.720	1.820	1.860
12			87.01%	88%	1.000	1.310	1.710	1.750	1.760	1.860	1.930
13			88.01%	89%	1.180	1.540	1.900	1.930	2.080	2.110	2.150
14			89.01%	90%	1.300	1.620	1.950	2.000	2.150	2.200	2.260
15			90.01%	91%	1.760	2.280	3.380	3.410	3.710	3.850	4.030
16			91.01%	92%	1.780	2.330	3.440	3.480	3.780	3.920	4.100
17			92.01%	93%	1.980	2.620	3.640	3.670	3.980	4.120	4.350
18			93.01%	94%	1.990	2.670	3.750	3.840	4.190	4.330	4.470
19			94.01%	95%	2.220	2.880	3.900	3.920	4.320	4.430	4.600
20
21			Example
22					Loan Amount	$450,000
23					Property Value	$500,000
24					Loan to Value %	90.00%
25
26					Loan Fee	$450,000 x 1.95% = $8,775
27
28
29
FEE

Cell Formulas
Range	Formula
F24	F24	=F22/F23

 

[jasonb75]

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%).

=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.

 

[twothings]

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%).