How to Transfer Matching Data Between Excel Sheets
Question details
The user needs to extract and transfer related data (such as License IDs corresponding to matching chassis numbers) from one spreadsheet to another, handling complex rules where lookup values might be located in multiple different columns.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing, matching, and extracting relational records across multiple tabs within a workbook.
- Observed behavior
- The source data has lookup values scattered across different columns (e.g., Column A or Column C), making standard single-column lookups insufficient to extract the correct License ID into the destination sheet.
Before applying any formulas, clearly define your matching rules: determine whether duplicate entries exist, which column should take priority if a match is found in multiple places, and ensure there are no trailing spaces in your text data.
Use Nested XLOOKUP for Multiple Source Columns
When your lookup value might appear in one of several columns, you can nest XLOOKUP functions to search the first column, and if not found, search the second.
A standard lookup formula only searches one column at a time. By nesting XLOOKUP functions, you can instruct the spreadsheet to first check Column A, and if no match is found, check Column C to retrieve the associated License ID.
Open Sheet2 and click on the cell where you want the extracted License ID to appear.
Type the formula: =XLOOKUP(A2, Sheet1!A:A, Sheet1!B:B, XLOOKUP(A2, Sheet1!C:C, Sheet1!B:B, "Not Found")). In this example, 'A2' is the chassis number you are searching for, and 'B:B' contains the License IDs in Sheet1.
Press Enter to see the result. Select the cell, then double-click the fill handle in the bottom-right corner to copy the formula down to the rest of the rows.
Use the FILTER Function for Multiple Matches
If a chassis number appears multiple times and you need to retrieve all corresponding License IDs, use the FILTER function.
Seamlessly Transfer and Analyze Data with WPS Spreadsheet
WPS Spreadsheet offers comprehensive support for advanced data lookup and extraction functions, making it effortless to organize and match complex datasets across multiple sheets.
- 1. Open Your Data File: Launch WPS Office and open your .xlsx workbook containing the source and destination sheets.
- 2. Access the Formula Tab: Navigate to the target sheet, select the cell for your extraction, and click the 'Formulas' tab on the top ribbon.
- 3. Insert a Lookup Function: Click on 'Lookup & Reference' and select 'XLOOKUP' or 'VLOOKUP' from the dropdown menu to open the formula dialog box.
- 4. Define Matching Criteria: Use the intuitive dialog box to select your lookup value, lookup array, and return array across your different sheets, then click OK to extract the matching data.

Frequently Asked Questions
What should I do if my lookup formula returns an #N/A error?
An #N/A error means the formula could not find an exact match. This often happens due to hidden trailing spaces or formatting mismatches (e.g., numbers stored as text). Try using the TRIM() function to clean your source data or ensure both columns share the same number format.
Can I transfer matching data between two different workbooks?
Yes. You can reference ranges in completely different workbook files within your formulas. However, for real-time updates and to prevent reference errors, it is recommended to have both files open simultaneously when building and calculating the formulas.
Which function is better for cross-sheet data transfer: VLOOKUP or XLOOKUP?
XLOOKUP is generally superior. It can search both horizontally and vertically, defaults to exact matches, allows searching right-to-left, and natively handles 'Not Found' errors, making it far more robust than the older VLOOKUP function.
How do I ensure the correct row supplies the data when there are duplicate entries?
If there are duplicates, standard lookup functions will always return the first match starting from the top. To pull data from a specific row among duplicates, you will need to apply a secondary sorting criteria, or use an INDEX/MATCH combination alongside additional unique identifiers.




