Two-way lookup with INDEX and MATCH approximate

Here we have a simple cost calculator which looks up cost based on a material’s width and height.

The match needs to be approximate.

If the width is 450, and the height stays at 325, the correct result is $3,600.

We can build a formula that does this lookup using INDEX and MATCH.

First, let’s getINDEXworking as a proof-of-concept by hard-coding part of the formula.

This is a great way to verify you have the right idea before you dive in.

This works fine, but of course it won’t change since the values are hard-coded.

So next, let’s set up the MATCH functions we need to calculate these values.

For match_type, we want to use 1 for approximate match because the values are sorted in ascending order.

The result is 4.

Again, the match_type is set to 1 for approximate match.

The result is 3.

These values are exactly what we need for INDEX.

So now I’ll simply copy and paste the MATCH functions into the original INDEX formula.

Width goes in for row_number.

And height goes in for column_number.

We now have a dynamic lookup that correctly calculates cost based on width and height using approximate matching.