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.
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.
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.
To lookup in value in a table using both rows and columns, you can build a formula that does a two-way lookup with INDEX and MATCH. In the example shown, the formula in J8 is: = INDEX ( C6:G10 , MATCH ( J6 , B6:B10 , 1 ), MATCH ( J7 , C5:G5 , 1 ))...
This example shows how to use INDEX and MATCH to retrieve a grade from a table based a given score. This requires an "approximate match", since it is unlikely that the actual score exists in the table. The formula in cell F5 is: = INDEX ( C5:C9 ,...
To highlight rows and columns associated with an approximate match, you can use conditional formatting with a formula based on the LOOKUP function together with with a logical function like OR or AND. In the example shown, the formula used to apply...
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, the INDEX...