The challenge is to pull the correct price based on both inputs.
At the core of this solution is a standard INDEX and MATCH formula.
The “step” approach is used tofetch tax rates dynamicallybased on the selected taxpayer status and year.
Inside the INDEX function, thearrayis given as the named rangeprices(C5:C16).
This formula works great.
This means the formula only works for Tier 1 pricing (Bronze).
This is where the idea of the numeric step adjustment comes into play.
Let’s break it down logically.
The 0 ensures an exact match.
The output from MATCH looks like this:
The next step is to subtract 1.
By adding this calculated step adjustment, the formula correctly shifts the lookup row to match the selected tier.
A step-based lookup formula is a simple but powerful way to efficiently look up information without restructuring the data.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
