Data Import & Export

How to Transfer Matching Data Between Excel Sheets

Huma Ashraf ChHuma Ashraf Ch Nov 11, 2026 868 views

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.

How to Transfer Matching Data Between Excel Sheets
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 you start

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.

Solution 1Recommended

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.

1
Select the Destination Cell

Open Sheet2 and click on the cell where you want the extracted License ID to appear.

2
Input the Nested XLOOKUP Formula

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.

3
Apply to Remaining Rows

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.

Modern Formula Alternative: If you are using an older version of Excel that does not support XLOOKUP, you can achieve the same result using nested IFERROR and VLOOKUP functions: =IFERROR(VLOOKUP(A2, Sheet1!A:B, 2, FALSE), VLOOKUP(A2, Sheet1!C:D, 2, FALSE)).
Advanced Data Management

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. 1. Open Your Data File: Launch WPS Office and open your .xlsx workbook containing the source and destination sheets.
  2. 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. 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. 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.
Fully compatible with Microsoft Excel (.xlsx) files and standard spreadsheet formulas.Natively supports powerful modern functions like XLOOKUP, FILTER, and dynamic arrays for complex matching scenarios.Lightweight, fast, and optimized for handling large datasets without performance lag.Built-in advanced data validation and duplicate removal tools to keep your records clean.
microsoft office alternative - wps office

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.