Function Problems

How to Return Project Status with a Date-Range Lookup in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to retrieve a project status based on matching an account number and ensuring an invoice date falls between the project start and end dates.

Product: Excel
Device & OS: not provided
Scenario: Performing a multi-criteria lookup to find a specific status based on an exact text match and a date range constraint across two separate tables.
Observed behavior: The user requires a formula solution to evaluate multiple columns in a projects table and return the correct status value back to the transactions table.

Before you start

Ensure both your transactions and projects datasets are formatted as Excel Tables (using Ctrl+T) and properly named so the structured references in the formula work seamlessly.

Solution 1Recommended

Use the FILTER Function for Multi-Criteria Lookup

The FILTER function combined with Boolean logic (multiplication of conditions) is the most effective way to perform a date-range lookup in modern Excel.

By multiplying conditions within the FILTER function, Excel treats each condition as a TRUE/FALSE (1/0) array. Only rows where all conditions are met (evaluate to 1) are returned. The final argument in the formula handles instances where no match is found, keeping your data clean.

Add a new column

Open your transactions table and add a new column named STATUS.

Enter the FILTER formula

Select the first cell in the STATUS column and input the formula: =FILTER(PROJECTS[STATUS],(PROJECTS[ACCOUNT]=[@ACCOUNT])*(PROJECTS[START DATE]<=[@[INVOICE DATE]])*(PROJECTS[END DATE]>=[@[INVOICE DATE]]),"")

Apply and verify

Press Enter to execute the formula. The correct project status will automatically populate for accounts where the invoice date falls within the active project timeline.

Clean Formula Output: If no criteria are met, the formula returns a blank string instead of an error, ensuring clean and readable data.

Advanced Data Lookup

Master Advanced Data Lookups with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for dynamic array functions like FILTER, enabling you to effortlessly execute complex date-range lookups and multi-criteria queries without slowing down your computer.

1. Open your file: Launch WPS Spreadsheet and open your workbook containing the transactions and projects data.
2. Format data as tables: Select your datasets and press Ctrl+T to convert them into official Tables, naming them PROJECTS and TRANSACTIONS for easy reference.
3. Input the array formula: Type the FILTER formula combining your account matching and date range conditions, then press Enter to populate the results immediately.

Highly compatible with Microsoft Excel formulas, functions, and table structures.Effortlessly handle multi-criteria lookups like FILTER, XLOOKUP, and INDEX/MATCH.Lightweight, fast, and completely free for everyday data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

What if I am using an older version of Excel that lacks the FILTER function?

In older Excel versions, you can use an array formula combining INDEX and MATCH with Boolean logic, or utilize the LOOKUP function such as: =LOOKUP(2,1/((PROJECTS[ACCOUNT]=[@ACCOUNT])*(PROJECTS[START DATE]<=[@[INVOICE DATE]])*(PROJECTS[END DATE]>=[@[INVOICE DATE]])),PROJECTS[STATUS]).

Why does my formula return a #CALC! error?

The #CALC! error typically occurs in the FILTER function when no matching data is found. By adding ",""" as the last argument in the formula, you instruct the spreadsheet to return a blank cell instead of the error.

Can I perform this lookup using regular cell ranges instead of tables?

Yes, you can substitute the structured references (like PROJECTS[STATUS]) with absolute cell references (e.g., $A$2:$A$100). Ensure you lock the references with dollar signs so the ranges don't shift when copying the formula down the column.

Excel & Data Problems