Explanation
In this example, the goal is to perform a two-way lookup, sometimes called a matrix lookup. This means we need to create a match on both rows and columns and return the value at the intersection of this two-way match
The core of this formula is INDEX, which is simply retrieving a value from C6:G10 (the "data") based on a row number and a column number.
=INDEX(C6:G10,row,column)
To get the row and column numbers, we use the MATCH function configured for an approximate match by setting the match_type argument to 1:
MATCH(J6,B6:B10,1) // get row number
MATCH(J7,C5:G5,1) // get column number
In the example, MATCH will return 2 when the width is 290, and 3 when the height is 300.
In the end, the formula reduces to:
=INDEX(C6:G10, 2, 3)
= 1800
Related formulas
VLOOKUP two-way lookup
INDEX and MATCH descending order
Related functions
INDEX Function
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....
MATCH Function
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 with the...
Related videos
How to do a two-way lookup with INDEX and MATCH
