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. 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 hard-coding 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 hard-coded. 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 the MATCH function with the lookup value from M8, and height values from row 6. Again, the match_type is set to 1 for approximate match. The result is 3. Now if I change the 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
