+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    05-17-2020
    Location
    Australia
    MS-Off Ver
    Current
    Posts
    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.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,216

    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.
    Attached Files Attached Files
    Last edited by belinda200; 05-17-2020 at 02:36 PM. Reason: Revised the formula

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return Value Based on Several Criteria in Several Identically Formatted Data Sets
    By ndudley26 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-03-2018, 11:13 AM
  2. [SOLVED] Return Value based on Two Dynamic Sets of Criteria (Non-VBA)
    By derrickb0690 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2018, 05:00 PM
  3. [SOLVED] Return value based upon lookup value and ranged date
    By clemsoncooz in forum Excel General
    Replies: 7
    Last Post: 07-30-2014, 06:14 PM
  4. [SOLVED] Return a Value in a Table based upon certain criteria
    By Staci in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-27-2014, 08:02 PM
  5. [SOLVED] Copy adjacent cell data based on ranged criteria.
    By Nneakz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-05-2013, 03:30 PM
  6. Replies: 6
    Last Post: 05-21-2012, 11:51 PM
  7. Return number OR sum based on two criteria
    By John in MN in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-26-2006, 09:25 AM

Bookmarks

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