Excel Multi-Criteria Lookup Guide

The VLOOKUP function in Microsoft Excel is one of the most commonly used tools for retrieving data from a table. It works well when matching a single value in one column and returning a related value from another column. However, real-world data rarely fits such simple patterns. In many cases, Excel must evaluate multiple conditions before returning a result.

For example, a worksheet may need to identify which date range a transaction falls into, determine pricing based on both region and product, or return a value only when two or more criteria are satisfied at the same time. Standard VLOOKUP cannot handle these scenarios without helper columns or complex workarounds.

This guide demonstrates three ways to perform a multi-criteria lookup in Excel using SUMPRODUCT, INDEX with MATCH, and XLOOKUP. Each method evaluates multiple conditions and returns a single matching value. While the formulas differ in structure and version compatibility, they solve the same core problem.

Scenario Overview

Consider a reporting model where transactions must be assigned to defined financial or operational periods. Each period has a start and end date, and every transaction date must be classified into the correct period based on where it falls within those ranges. Instead of manually reviewing each transaction, Excel can determine the appropriate period automatically.

The workbook contains two Excel Tables.

The first table, Periods (TBL_PERIODS), defines the valid date ranges and contains:

• ID: A unique identifier for each period.
• Start Date: The beginning of the period.
• End Date: The end of the period.

Each row represents one date range. The ranges do not overlap and each ID is unique.

The second table, Transactions (TBL_TRANSACTIONS), contains:

• Transaction Date: The date to classify.
• Period ID: The result column where the matching ID will be returned.

The objective is to determine which ID in TBL_PERIODS corresponds to each Transaction Date value in TBL_TRANSACTIONS.

A Transaction Date belongs to a period when it is greater than or equal to the Start Date and less than or equal to the End Date. Because the ranges do not overlap, each transaction produces exactly one match.

The formulas below use structured references such as TBL_PERIODS[Start Date] and [@[Transaction Date]]. Structured references improve readability and automatically adjust as tables grow.

NOTE: These formulas assume only one row matches the conditions. If multiple rows qualify, results may be unpredictable.

Method 1: Multi-Criteria Lookup With SUMPRODUCT

Enter the following formula in the Period ID column of TBL_TRANSACTIONS:

=SUMPRODUCT(
  --(TBL_PERIODS[Start Date] <= [@[Transaction Date]]),
  --(TBL_PERIODS[End Date] >= [@[Transaction Date]]),
  TBL_PERIODS[ID]
)

This formula evaluates every row in TBL_PERIODS against the current row’s Transaction Date.

The first condition checks whether the transaction date is on or after the period’s start date. The second checks whether it is on or before the end date. When both conditions are TRUE for the same row, the double minus operator converts those TRUE values into 1. All other rows evaluate to 0.

SUMPRODUCT multiplies the logical results by the corresponding ID values and returns the total. Since the date ranges do not overlap, only one row satisfies both conditions, and only one ID contributes to the result.

This method works in older versions of Microsoft Excel and does not require array entry. It is compact and reliable, though the logic may appear less intuitive at first.

NOTE: Return value must be numeric.

SUMPRODUCT performs arithmetic calculations. The ID column must contain numeric values. If the ID contains text such as P1 or 2024-Q1, the formula will return an error because text cannot be multiplied.

If the Return Value Is Text

When the ID column contains text values, SUMPRODUCT must return the matching row position instead of multiplying by the ID. INDEX can then retrieve the text value.

Enter the following formula in the Period ID column of TBL_TRANSACTIONS:

=INDEX(
  TBL_PERIODS[ID],
  SUMPRODUCT(
    --(TBL_PERIODS[Start Date] <= [@[Transaction Date]]),
    --(TBL_PERIODS[End Date] >= [@[Transaction Date]]),
    ROW(TBL_PERIODS[ID])
  )
  - MIN(ROW(TBL_PERIODS[ID])) + 1
)

In this version, SUMPRODUCT identifies the matching row number. INDEX then returns the text-based ID from that row.

When working with text return values, the INDEX with MATCH or XLOOKUP methods are usually cleaner and easier to maintain. SUMPRODUCT is best suited for numeric return values.

Method 2: Multi-Criteria Lookup With INDEX and MATCH

Enter the following formula in the Period ID column of TBL_TRANSACTIONS:

=INDEX(
  TBL_PERIODS[ID],
  MATCH(
    1,
    (TBL_PERIODS[Start Date] <= [@[Transaction Date]]) *
    (TBL_PERIODS[End Date] >= [@[Transaction Date]]),
    0
  )
)

This approach separates the lookup into two steps.

The logical conditions generate arrays of TRUE and FALSE values. Multiplying the arrays converts rows where both conditions are TRUE into 1. All other rows evaluate to 0.

MATCH searches for the value 1 and returns its position. INDEX then retrieves the corresponding ID from TBL_PERIODS.

In modern Excel versions with dynamic arrays, the formula works with a normal Enter key press. In older versions, confirm it using Ctrl + Shift + Enter. If entered incorrectly in legacy Excel, the result may display as #N/A.

This method works with both numeric and text return values and is widely recognized for its flexibility.

Method 3: Multi-Criteria Lookup With XLOOKUP

In modern versions of Excel, XLOOKUP provides the most readable solution.

Enter the following formula in the Period ID column of TBL_TRANSACTIONS:

=XLOOKUP(
    1,
    (TBL_PERIODS[Start Date] <= [@[Transaction Date]]) *
    (TBL_PERIODS[End Date] >= [@[Transaction Date]]),
    TBL_PERIODS[ID]
)

This formula uses the same logical comparison as the previous method. The two conditions generate an array that equals 1 only when the transaction date falls within the defined period range. XLOOKUP searches for 1 and returns the corresponding ID from TBL_PERIODS.

No special key combinations are required. The formula is easier to read than nested INDEX and MATCH functions and does not rely on column index numbers. It works with both numeric and text return values.

For current Excel versions, this is typically the preferred approach.

Summary

When VLOOKUP cannot handle multiple criteria, SUMPRODUCT, INDEX with MATCH, and XLOOKUP provide effective alternatives. SUMPRODUCT works well for numeric results, INDEX with MATCH offers flexibility, and XLOOKUP provides the cleanest solution in modern Excel. Understanding these techniques makes it possible to solve complex lookup problems with ease.