How to Create Two-Dimensional Lookups in Excel Formulas

by Charley Kyd on November 16, 2011

This figure illustrates a great Excel question that a friend asked today.

His original question was, “In this table, how can I return the date where the lowest value occurs?”

More generally, however, he was asking how to look up a value in two dimensions. 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:

Then I started to build up the formulas.

The SUMPRODUCT function is the key, because it’s the only function that works like an array formula without needing to be array-entered. For example, 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)

My friend would enter =MIN(Data) in cell A10, but you could enter any lookup value you want.

Tagged as: array formulas, DATEVALUE, INDEX, lookup functions, SUMPRODUCT