Function Problems

How to Perform an Excel Two-Criteria Lookup Using Project and Date

Huda Qurayshi Sep 27, 2026 869 views

Question details

The user needs to retrieve an update from a Change Log table by matching both a project name and an update date against a Projects table, but is encountering function errors.

How to Perform a Two-Criteria Lookup Using Project and Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Pulling specific project status updates from a changelog by matching two distinct data points (Project Name and Date) simultaneously.
Observed behavior: Standard formulas are returning an invalid function error or a #NAME? error, especially when array formulas or specific syntaxes are incorrectly applied.

Before you start

Ensure both your source data and criteria tables have identically spelled project names and matching date formats. Check if you are using an older version of Excel, as you may need to use specific keystrokes to activate array formulas.

Solution 1Recommended

Use INDEX and MATCH with Multiple Criteria (Array Formula)

This is the most universally compatible method for older and newer spreadsheet versions to look up data using multiple conditions.

By combining the INDEX and MATCH functions with boolean logic, you can force the spreadsheet to evaluate both the project name and the date simultaneously.

Select the target cell

Click on the empty cell in your Projects table where you want the Change Log update to appear.

Enter the formula structure

Type the formula following this structure: =INDEX(Return_Range, MATCH(1, (Project_Range="Project Name") * (Date_Range=Date_Value), 0)).

Execute as an Array Formula

If you are using an older version of Excel, do not just press Enter. Instead, press Ctrl+Shift+Enter. Curly braces {} will automatically appear around the formula, indicating it is calculating as an array.

Use INDEX and MATCH with Multiple Criteria (Array Formula)

Troubleshooting #NAME? Errors: If you receive a #NAME? error, double-check for typos in your function names and ensure any named ranges you referenced actually exist in the workbook.

Solution 2

Use a Helper Column with VLOOKUP

A simpler alternative if array formulas are too complex or if your version of Excel does not support XLOOKUP.

Advanced Spreadsheet Features

Perform Complex Lookups Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, XLOOKUP, and multi-criteria INDEX/MATCH functions out of the box. It offers a smooth, error-free environment for matching project logs and dates.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your Projects and Change Log tables.
2. Access the Function Library: Navigate to the Formulas tab on the top ribbon and click on 'Insert Function'.
3. Build your formula securely: Search for INDEX or XLOOKUP in the dialog box. The built-in wizard will guide you through selecting your criteria ranges without risking syntax errors.
4. Save and share: Save your completed document seamlessly in .xlsx format to share with Microsoft Office users without compatibility loss.

Fully compatible with Microsoft Excel formulas and functions (.xlsx).Natively supports advanced lookup functions like XLOOKUP without complex arrays.Built-in formula error checking helps quickly identify #NAME? and syntax issues.Lightweight, fast, and free to use across Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my multi-criteria lookup formula return a #NAME? error?

A #NAME? error usually occurs if a function name is misspelled (e.g., typing INDX instead of INDEX) or if you reference a named range that hasn't been defined in your workbook. Check your spelling and table references carefully.

Do I always need to press Ctrl+Shift+Enter for two-criteria lookups?

No. If you are using newer software that supports dynamic arrays or the XLOOKUP function, you can simply press Enter. You only need Ctrl+Shift+Enter in older spreadsheet versions when using INDEX/MATCH with multiple conditions.

Can I use XLOOKUP for two criteria?

Yes. You can use the ampersand (&) to join criteria within XLOOKUP. The syntax looks like this: =XLOOKUP(Criteria1&Criteria2, Range1&Range2, ReturnRange). This is often easier than using INDEX and MATCH.

Excel & Data Problems