# Excel Lookup With Multiple Criteria Using SUMPRODUCT or Array Formula

The `VLOOKUP` function in Excel is very useful until there is a need to identify data satisfying multiple criteria. In the following example, I’ll demonstrate two different methods to perform a lookup against data that must meet multiple conditions. One method uses the `SUMPRODUCT` function and the other uses an array formula with `MATCH` and `INDEX` functions.

This example assumes that the data is structured in a way that will provide a unique result from the lookup. The standard use of the `SUMPRODUCT` and array formulas have a very different intent so if the result is not unique, you may see unexpected results.

In the sample worksheet below, there is a set of three periods specified. Column A contains the period number which is a simple sequential number representing a unique ID for the period. Column B and Column C define start and end dates for each period. There are no overlapping dates between each of the defined periods. Column E contains a small list of individual dates. I want to automatically identify which period the date in Column E belongs to in Column F. In order to belong to a period, the specific date in Column E should be greater than or equal to the period start date and less than or equal to the period end date.

``=SUMPRODUCT(--(\$B\$2:\$B\$4<=E2),--(\$C\$2:\$C\$4>=E2),\$A\$2:\$A\$4)``
``=INDEX(\$A\$2:\$A\$4,MATCH(1,((\$B\$2:\$B\$4<=E2)*(\$C\$2:\$C\$4>=E2)),0))``