How to Use SUMPRODUCT to Create Two-Dimensional Lookups in Excel Formulas

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:

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

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.