How to Pull an Invoice Date from Another Excel Worksheet in WPS Office
Question details
The user wants to return an invoice date from a source worksheet by matching an invoice number shared between two worksheets.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Two worksheets contain invoice data; the destination sheet has an invoice number and needs the corresponding date from the source sheet.
- Observed behavior
- The expected result is that the formula returns the related invoice date after matching the invoice number in the source data.
Place both worksheets in the same workbook and verify that the invoice number column exists in both sheets with matching values. If the source data is in another workbook, open it in WPS Office Excel so you can reference it or consolidate it first.
Use INDEX and MATCH to Match the Invoice Number
Best for WPS Office Excel and older Excel versions where XLOOKUP is not available; it works when the invoice number is in a known column and the date is in another column.
The MATCH function finds the row position of the invoice number in the source column, and INDEX returns the value from the same row in the date column. Locking the source columns with dollar signs keeps the lookup range fixed when you copy the formula.
Click the cell where you want the first invoice date to appear, for example C2 on the destination sheet.
Type =INDEX(Sheet2!$E:$E,MATCH($C2,Sheet2!$D:$D,0)) and replace Sheet2, column E, and column D with your actual sheet name and columns.
Press Enter to return the first date, then drag the fill handle down to apply the formula to other invoice rows.
Use XLOOKUP in Supported Versions
Use this if you are on Microsoft 365 or a WPS Office version that supports XLOOKUP and prefer a simpler formula.
Pull Invoice Dates Across Worksheets with WPS Office Excel
Open your workbook in WPS Office Excel and use INDEX and MATCH to pull invoice dates automatically. Once the formula is set, WPS Office copies it down for every invoice while keeping full compatibility with Microsoft Excel workbooks.
- 1. Open the workbook: Launch WPS Office Excel and open the workbook that contains both worksheets.
- 2. Select the output cell: Click the cell in the destination sheet where you want the first invoice date to appear.
- 3. Enter the lookup formula: Type =INDEX(Sheet2!$E:$E,MATCH($C2,Sheet2!$D:$D,0)) and adjust the sheet name and column letters to your layout.
- 4. Confirm the formula: Press Enter to pull the invoice date for the first invoice number.
- 5. Fill down for the remaining invoices: Drag the fill handle down the column to apply the formula to the remaining invoice rows.
Frequently Asked Questions
What does the formula =INDEX(Sheet2!$E:$E,MATCH($C2,Sheet2!$D:$D,0)) do?
MATCH finds the row position of the invoice number in column D of Sheet2, and INDEX returns the value from that same row in column E, which is the invoice date. Adjust Sheet2, D, and E to your workbook.
Can I pull an invoice date from a different workbook?
Yes, but the source workbook must be open in WPS Office Excel if you reference it directly. You can also move the source sheet into the same workbook to simplify the formula.
Why does the formula return #N/A?
It means the invoice number in the destination cell was not found in the source invoice number column. Check for extra spaces, different number formats, or a typo in the invoice number.
Does WPS Office support XLOOKUP?
Newer WPS Office versions support XLOOKUP. If your version does not, use INDEX and MATCH, which works in WPS Office Excel and Microsoft Excel.
