Function Problems

How to Use XLOOKUP to Return an Accrual Date from Another Excel Sheet

Phi Hung Vo Sep 25, 2026 869 views

Question details

The user needs to retrieve accrual dates from a separate worksheet by matching ID numbers using the XLOOKUP function.

How to Use XLOOKUP to Return an Accrual Date from Another Excel Sheet

Product: Spreadsheets
Device & OS: not provided
Scenario: Consolidating employee or item data by looking up matching ID numbers across different worksheets within the same workbook.
Observed behavior: The user requires the correct XLOOKUP formula syntax for cross-sheet references and is encountering #VALUE! or #NAME? errors due to mismatched range sizes or misspelled function names.

Before you start

Ensure both worksheets are within the same workbook, and verify that the ID columns used for matching contain identical data types (e.g., both formatted as Text or both as Numbers).

Solution 1Recommended

Construct an XLOOKUP Formula with Absolute References

Use the XLOOKUP function by selecting lookup and return arrays across your different sheets, ensuring both arrays are locked with absolute references so they do not shift.

XLOOKUP is a powerful function that replaces VLOOKUP. When pulling data from other sheets, it is critical to ensure that your lookup array and return array have the exact same number of rows. Locking these ranges with absolute references ensures accuracy when copying the formula down.

Select the destination cell

Click on the cell where you want the accrual date to appear and type =XLOOKUP( to begin the formula.

Select the lookup value

Click on the cell containing the ID you want to match (for example, C2), then type a comma.

Define the lookup array

Navigate to the sheet containing the list of all IDs (e.g., 'Time Off Balance Detail'). Select the ID range and press F4 to make it absolute (e.g., $C$2:$C$567), then type a comma.

Define the return array

Navigate to the sheet containing the dates (e.g., 'PTO Accrual Date'). Select the date range and press F4 to make it absolute (e.g., $D$2:$D$567).

Add the 'if not found' argument

Type a comma, followed by "" to tell Excel to return a blank cell if the ID is not found. Close the formula with a parenthesis so it looks like: =XLOOKUP(C2,'Time Off Balance Detail'!$C$2:$C$567,'PTO Accrual Date'!$D$2:$D$567,"") and press Enter.

Construct an XLOOKUP Formula with Absolute References

Format as Date: If your result appears as a random 5-digit number, select the cell, go to the Home tab, and change the Number Format from General to Short Date.

Solution 2

Troubleshoot #VALUE! and Name Errors

Fix common mistakes that cause errors when writing an XLOOKUP formula, such as mismatched array sizes or spelling mistakes.

Master Spreadsheets with WPS Office

Easily Manage Data Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports modern functions like XLOOKUP, allowing you to seamlessly pull data across multiple sheets and workbooks. It provides intuitive tooltip guides and point-and-click range selection to prevent array dimension mismatches.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your ID and date data.
2. Start the XLOOKUP function: Select your target cell and type =XLOOKUP( to trigger the interactive formula tooltip.
3. Select your arguments: Use your mouse to follow the on-screen prompts, clicking seamlessly between sheets to select your lookup value, lookup array, and return array.
4. Complete the calculation: Press Enter to finalize the formula, then double-click the fill handle in the bottom-right corner of the cell to apply it to all rows.

Fully compatible with Microsoft Excel formulas including XLOOKUPSmart formula tooltips to guide you through complex argumentsBuilt-in error checking to quickly spot array size mismatchesLightweight application with a familiar, easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return a number instead of an accrual date?

Spreadsheet software stores dates as sequential serial numbers for calculation purposes. To view it as a date, select the cell containing the formula, navigate to the Home tab, click the Number Format dropdown, and choose Short Date or Long Date.

How can I prevent XLOOKUP from displaying #N/A when an ID is missing?

XLOOKUP includes a built-in argument for missing data. In the fourth argument of your formula, simply add quotation marks with your desired text or keep them empty for a blank cell. For example: =XLOOKUP(C2, Sheet2!A:A, Sheet2!B:B, "Not Found").

Can I use XLOOKUP to return data from another entirely separate workbook?

Yes. While writing the XLOOKUP formula, keep both workbooks open. When you need to select the lookup or return array, simply click over to the other workbook and select the range. The formula will automatically generate the correct external reference file path.

Excel & Data Problems