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
Explanation
This is a standard VLOOKUP formula. It requires a table with lookup values (in this case, dates) to the left of the values being retrieved.
The lookup value comes from cell E6, which must be a valid date. The table array is the range B6:C11, and the column index is 2, since the amounts are in the second column of the table. Finally, zero is provided for the final argument to force an exact match.
The VLOOKUP function locates the date value for Sept 4, and returns the value at the same row in the second column: 12,500.
Note: the lookup value and the date values in the table must be valid Excel dates.
