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.
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, ising approximate matching.
The Excel INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined...