Exceljet

Quick, clean, and to the point

This video is part of our online video training.

Two-way lookup with INDEX and MATCH approximate

Tags 
Summary 
In this video, we'll look at how to build a two-way lookup with INDEX and MATCH, using approximate matching. This is a classic solution for non-exact lookups like income tax, shipping costs, material costs, etc.
Video Transcript 

Two-way lookup with INDEX and MATCH approximate

In this video, we'll look at how to build a two-way lookup with INDEX and MATCH, using an an approximate match.

Here we have a simple cost calculator, which looks up cost based on on a material's width and height. The match needs to be approximate. For example, if the width is 250, and the height is 325, the correct result is $1,800.

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 get INDEX working as a proof of concept, by hardcoding part of the formula. This is a great way to make sure you have the right idea, before you start.

So, with the data in our table as the array, and with a width of 450 and a height of 325, INDEX is going to need a row number of 3, and a column number of 4, to retrieve the correct value in the table. This works fine but of course it won't change since the values are hardcoded.

=INDEX(C7:I14,3,4) // hardcoded row and column

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

To get the value for width, which is the row number in INDEX, we'll use the lookup value from M7, and the values in column B, as the lookup array. For match type, we want to use 1 for approximate match because the values are sorted in ascending order. The result is 4.

=MATCH(M7,B7:B14,1)

To get height, which is the column inside INDEX, we'll again use MATCH with the value from M8, the heights values from row 6. Again, the match type again set to 1 for approximate match. The result is 3.

=MATCH(M8,C6:I6,1)

Now if I change width to 350 and height to 550, we get a new set of results.

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.

=INDEX(C7:I14,MATCH(M7,B7:B14,1),MATCH(M8,C6:I6,1))
Author 
Dave Bruns

Related shortcuts

Just wanted to say that your site is really, really helpful. Great work and thank you! -Lloyd
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course