# How to return a number based on two sets of ranged criteria in a table

1. ## How to return a number based on two sets of ranged criteria in a table

Hi everyone,

I'm trying to build a mortgage analysis excel and part of it requires calculating the LMI (lender's mortgage insurance). This is calculated as detailed in the attached table. Note that you are unable to attain LMI if the LVR (loan to value ratio) is >95%, LMI is not necessary if LVR is <80%

I'll summarise and give an example of how the table works, say you purchase a property for \$1,000,000, you have a deposit of \$150,000 so you'll be borrowing \$850,000, meaning your LVR is 85%. You then scan down columns B-C until you find which range the LVR value falls under (85% i.e. between cell b9 and c9), you then scan along rows 2-3 until you find the amount you're borrowing (\$850,000, i.e. between celll h2 and h3). You then multiply 1.407% to the borrowed amount of \$850,000, yielding an amount payable for LMI of ~\$12,000

I was originally doing an ifand function, however, excel wouldn't accept the formula because it was over 65 nests. The ifand function is about the extent of my excel knowledge, so please go easy on me!  Register To Reply

2. ## Re: How to return a number based on two sets of ranged criteria in a table

C25 =INDEX(\$B\$3:\$H\$19,Match(C24,\$B\$3:\$B\$19,1),match(C23,\$B\$3:\$H\$3,1))

The result is 1,333%

See the attached file.

If you expect another result, you have to change the values in B5:B19  Register To Reply

3. ## Re: How to return a number based on two sets of ranged criteria in a table

Hi,

A little similar to Oeldere's ,but adding the LOOKUP function for the searching value to perform an approximate match (as we are searching on ranges): ``Please Login or Register  to view this content.``  Register To Reply