Help with formula - lookup results of one formula to a table and amend results as required

[xtian88]

Hi,

I've built out a mortgage calculator spreadsheet to have all sorts of variables and customised to what I need. There's one final piece of the puzzle that I just can't work out. 

2 of the many columns I've built are purchase price and stamp duty columns. The cells in those columns already have existing formulas in them to calculate those amounts.

I've found the stamp duty calculation formula, however there is a specific concession for purchase prices of between $600,001 and $750,000. I cannot find the formula they use for these figures, only their own online calculator and a table of examples. Nevertheless, using their calculator I put a concession table together with results for 600-750k in 10k increments. This table lives in the same sheet as the main calculator I built.

The current formula in the 'stamp duty' column is =SUM([@[Purchase price]]-130000)*0.06+2870

What I can't work out is how to set an if condition on the result of that formula. Basically, if the "purchase price" value in the stamp duty formula matches one of the values in the concession table column 1, then I want it to take the number from the concession table column 2 on the same row as the matched value.

E.g. Purchase price = 700k, the stamp duty formula recognises this value is in the concession table and instead of using the standard calc, it refers to the concession stamp duty table to get the appropriate value. 

I'm sure it's possible, but it's well, well above my level of knowledge for excel! 

Thank you in advance for any help, I've attached a screenshot to hopefully make more sense of it.

[#2]

@xtian88 

Why invent something that already exists? :))

Mortgage Loan Calculator

Get a quick and clear picture of what it will take to pay off your mortgage with this accessible mortgage loan calculator template. This professional mortgage loan calculator template includes loan details, key statistics, and an amortization table.

You will find more Templates in the upper Link.

Hope I could help you with these information / links.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@xtian88 Created a small mock-up based on your screenshot (see attached). Up to you to decide if this is what need and, if so, to get the principle to work in your real schedule.

The formula for Total true cost looks like this:

=IF(
    AND(
         [@[Purchase price]]>=MIN(tblConncessions[Dutiable]),
         [@[Purchase price]]<=MAX(tblConncessions[Dutiable])
    ),
    INDEX(tblConncessions[Duty],MATCH([@[Purchase price]],
    tblConncessions[Dutiable],1)),([@[Purchase price]]-130000)*0.06+2870
)