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.
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.
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.