Search code examples
excelexcel-formulacalculationthreshold

Excel formula: how to find if a value is within a range of thresholds, & then return the corresponding row

I'm trying to calculate my student loan (lol). Below are the first few rows of legit data from my government, which don't seem to follow any pattern whatsoever.

Threshold ($) Repayment rate (%)
51,550 0
59,519 1
63,090 2
66,876 2.5
70,889 3
... ...

If my income is less than a certain threshold (first column), my repayment rate will be the corresponding value in the second column. For example:

  • Income = 50,000 < 51,550, so repayment rate = 0%.
  • Income = 51,550 < 59,519, so repayment rate = 1%.
  • Income = 59,518 < 59,519, so repayment rate = 1%.

I want to have a formula that returns the corresponding repayment rate for my income.

Because this involves comparing one value to 20 rows of data, I really hope that there can be a cleaner solution instead of a bunch of ifs. I only know the basics of Excel formulae, so I need help & guidance with this. Thanks a lot!

Solution

  • A simple vlookup() like so:

    VLOOKUP(E4,$A$3:$B$7,2,1)

    enter image description here