# 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!

Thank you in advance.

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

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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1