Excel formula: Two-way lookup with INDEX and MATCH

Generic formula

=INDEX(data,MATCH(val,rows,1),MATCH(val,columns,1))

Summary

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))

Note: this formula is set to "approximate match", so row values and column values must be sorted.

Explanation

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 MATCH, configured for approximate match, by setting the 3rd argument to 1 (TRUE):

MATCH(J6,B6:B10,1) // get row number
MATCH(J7,C5:G5,1) // get column number

In the example, MATCH will return 2 when width is 290, and 3 when height is 300.

In the end, the formula reduces to:

=INDEX(C6:G10, 2, 3)
= 1800

Author

Dave Bruns

Related formulas

Two-way lookup with VLOOKUP

This is a standard VLOOKUP exact match formula with one exception: the column index is supplied by the MATCH function. Note that the lookup array given to MATCH (B2:E2) representing column headers deliberately includes the empty cell B2. This is...

INDEX and MATCH descending order

This formula uses -1 for match type to allow an approximate match on values sorted in descending order. The MATCH part of the formula looks like this: MATCH ( F4 , B5:B9 , - 1 ) Using the lookup value in cell F4, MATCH finds the first value in B5:B9...

Related functions

Excel INDEX Function

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...

Excel 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...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Exceljet

Quick, clean, and to the point

Two-way lookup with INDEX and MATCH

Excel Formula Training

Download 200+ Excel Shortcuts

Excel video training