How to Match Dates Across Excel Sheets and Return Monthly Costs
Question details
The user needs to dynamically retrieve monthly project costs from a detailed worksheet and display them on a consolidated overview sheet based on changing project start dates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating monthly project costs from different worksheets where project timelines shift, requiring a formula that searches for a specific month and returns the corresponding cost rather than relying on static column references.
- Observed behavior
- Using fixed-column IF formulas fails to return the correct monthly cost when a project start date changes, as the formula continues to look at the original static cell instead of dynamically searching for the new date location.
Ensure that the dates on both your consolidated overview worksheet and your detailed cost worksheet share the exact same formatting (e.g., both formatted as Date values or identical text strings) so the lookup formula can successfully match them.
Use XLOOKUP to Match Dates and Return Costs Dynamically
Replace static IF formulas with XLOOKUP to automatically scan across rows for changing dates and return the matching monthly project costs.
XLOOKUP is ideal for this scenario because it dynamically searches a specified array for a value and returns the corresponding item from another array. When project start dates shift horizontally across columns, XLOOKUP will automatically locate the new column without requiring manual formula adjustments.
Open your consolidated overview worksheet and click the cell where you want the monthly cost to be displayed.
Type the formula =XLOOKUP(B4, Sheet1!B12:Z12, Sheet1!B40:Z40, 0) into the formula bar.
Replace 'B4' with the cell reference on your current sheet that contains the specific month or date you are searching for.
Change 'Sheet1!B12:Z12' to the row range containing your project dates on the source sheet, and change 'Sheet1!B40:Z40' to the row range containing the corresponding costs.
Press Enter to return the cost. You can then drag the fill handle across adjacent cells to apply the lookup formula for the remaining months.
Use INDEX and MATCH for Older Excel Versions
If you are using a version of Excel that does not support XLOOKUP, you can use the INDEX and MATCH functions combined to achieve the exact same dynamic horizontal lookup.
Seamlessly Consolidate Project Costs with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like XLOOKUP, INDEX, and MATCH natively. Consolidating financial data across multiple sheets is simple, fast, and completely compatible with standard spreadsheet formats.
- 1. Install WPS Office: Download and install the free WPS Office suite, then open your project workbook in WPS Spreadsheet.
- 2. Navigate to the overview sheet: Select your consolidated overview worksheet and click on the target cell where the monthly cost is needed.
- 3. Use the formula helper: Type =XLOOKUP( and utilize WPS Spreadsheet's intuitive formula tooltips to select your lookup date, date array, and cost array.
- 4. Instantly retrieve costs: Press Enter to execute the formula and instantly retrieve your dynamically matched project costs across sheets.
Frequently Asked Questions
Why does my standard IF function fail when the project start date changes?
A standard IF function (e.g., =IF(B4=Sheet1!B12, Sheet1!B40, 0)) checks a fixed cell reference. If your project date shifts from column B to column C, the formula continues to evaluate column B, resulting in an error or a zero. Lookup functions scan the entire range to dynamically find the new location.
Why is my XLOOKUP formula returning an #N/A error?
The #N/A error indicates that the target date cannot be found in the specified lookup range. Ensure that both sheets use the exact same date format (e.g., both are Date objects or both are Text) and check for hidden spaces or mismatched data types.
Can I use VLOOKUP instead to match dates across sheets?
VLOOKUP searches vertically down columns. Because your dates are arranged horizontally across a row with costs in a row below them, you would need HLOOKUP. However, XLOOKUP or INDEX/MATCH are generally preferred as they are more flexible and won't break if you insert new rows between your data.
