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.
Either formula should be entered starting in cell F2 (or modified to fit your particular needs).
Method 1 – Lookup using the SUMPRODUCT function
=SUMPRODUCT(--($B$2:$B$4<=E2),--($C$2:$C$4>=E2),$A$2:$A$4)Method 2 – Lookup using an array formula with MATCH and INDEX functions
=INDEX($A$2:$A$4,MATCH(1,(($B$2:$B$4<=E2)*($C$2:$C$4>=E2)),0))Since Method 2 uses an array formula, hit CTRL+SHIFT+ENTER after entering the formula in cell F2 otherwise Excel will show “#N/A” instead of the correct result.