Forum Discussion

xtian88's avatar
xtian88
Copper Contributor
Sep 27, 2021

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

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 ...
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 27, 2021

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
)

 

Resources