Advanced Lookups
How to Use SUMPRODUCT to Create TwoDimensional Lookups in Excel Formulas
You can use the SUMPRODUCT worksheet function to find a value in a table and return the row and column headers where it's found. Here's how.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

A friend recently asked,
"In this table, how can I return the
date where the lowest value occurs?"
More generally, however, he was asking how to look up any unique value in a table and return its row and column titles.
Here, for example, we want to look up the value of 180 and
return both March and 2010.
This is a different challenge than most Excel
lookups, which are limited to a single row or column.
To keep the explanation as simple as possible, I began
by defining four range names, as illustrated in the image
below. The range names are:
FindVal 
=Sheet1!$A$10 
Months 
=Sheet1!$B$2:$I$2 
Years 
=Sheet1!$A$3:$A$8 
Data 
=Sheet1!$B$3:$I$8 
I
entered the value 180 in cell A10, then I started to build up the formulas.
The SUMPRODUCT function is the key
to solving this problem, because it's the only
function that works like an array formula without needing to be
arrayentered.
To illustrate its power, this formula returns the number of
times that any value in the Data range has a value equal to
FindVal:
=SUMPRODUCT((Data=FindVal)*1)
The (Data=FindVal) piece returns an array of TRUE and FALSE
values. We need to multiply that array by 1 to convert the array
into one with 1 and 0 values, which can be counted.
Assuming for now that only one value matches FindVal, we can
find the row it's on using this formula:
=SUMPRODUCT(ROW(Data)*(Data=FindVal))
This works because the array returned by (Data=FindVal) has only
one TRUE value. When we multiply its row number by its TRUE
value, and then sum the results, the formula returns that single
row number.
However, like Excel's
MATCH function, we don't want the actual
row number, we want the index number within the Data
range...which also is the index number for the Years range.
Therefore we subtract the row number for the top row of the Data
range, and add 1:
=SUMPRODUCT(ROW(Data)*(Data=FindVal))ROW(Data)+1
This formula is somewhat equivalent to the MATCH function.
However, if the Data range has two values that match FindVal,
this formula returns an incorrect result. Therefore, we need to
add a test to ensure that only one such value exists:
=IF(SUMPRODUCT((Data=FindVal)*1)<>1,NA(),SUMPRODUCT(ROW(Data)*(Data=FindVal))ROW(Data)+1)
Finally, we can pass this value to an
INDEX function to return
the year value we need:
A11:
=INDEX(Years,IF(SUMPRODUCT((Data=FindVal)*1)<>1,NA(),
SUMPRODUCT(ROW(Data)*(Data=FindVal))ROW(Data)+1))
The version that returns the month value is very similar:
A12:
=INDEX(Months,IF(SUMPRODUCT((Data=FindVal)*1)<>1,NA(),
SUMPRODUCT(COLUMN(Data)*(Data=FindVal))COLUMN(Data)+1))
Finally, to answer my friend's original question, here's the
formula that returns the date serial number for where the value
in cell A10 can be found:
A13: =DATEVALUE(A12&""&A11)
To find the row and column headings for the lowest number, my friend would enter...
A10: =MIN(Data)
...in the cell shown, but you could
enter any lookup value you want.
