Exceljet

Quick, clean, and to the point

Two-way lookup with INDEX and MATCH

Excel formula: Two-way lookup with INDEX and MATCH
Generic formula 
=INDEX(data,MATCH(val,rows,1),MATCH(val,columns,1))
Explanation 

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 that this formula is doing and "approximate match", so row values and column values must be sorted.

How this formula works

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

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step 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. Start building valuable skills with Excel formulas today. Learn more.

Sir your website is awesome. It gave me what I want. Thanks and hope it will help me again when I need it - Atul
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course