Function Problems

How to Use Excel Lookup Formulas to Copy Data Between Worksheets

Partner Editor Oct 12, 2026 873 views

Question details

The user needs to populate blank cells on one worksheet by matching a common identifier with data on another worksheet, as the records are scattered across different rows.

How to Use Excel Lookup Formulas to Copy Data Between Worksheets

Product: Excel
Device & OS: not provided
Scenario: Consolidating scattered data (such as UPCs) into a master inventory list by matching unique item numbers from a secondary worksheet.
Observed behavior: The matching item numbers are located on completely different rows across the two sheets, preventing users from simply copying and pasting the data.

Before you start

Ensure both worksheets contain an identical column of unique identifiers (such as Item Numbers) and check that there are no hidden trailing spaces in these columns that could cause matching errors.

Solution 1Recommended

Use the VLOOKUP Function to Match Data

This is the most common method for pulling data across worksheets when your unique identifier is located in the first column of the reference dataset.

VLOOKUP searches for a specific value in the first column of a selected range and returns a value in the same row from a specified column.

To work correctly, the common identifier (like your item number) must exist in the very first column of the lookup range on your second worksheet.

Select the target cell

Click on the blank cell on Sheet 1 where you want the matching data (e.g., the UPC code) to be populated.

Enter the VLOOKUP formula

Type the formula =VLOOKUP(A2, Sheet2!$A$2:$C$500, 2, FALSE). Replace 'A2' with the cell containing the item number on Sheet 1, 'Sheet2!$A$2:$C$500' with the data range on your secondary sheet, and '2' with the column number containing the UPCs.

Apply the formula to the column

Press Enter to execute the formula. Click the cell, hover over the bottom-right corner to reveal the fill handle, and drag it down to apply the formula to the remaining rows.

Use Absolute References: Locking your data range with absolute references (the dollar signs like $A$2:$C$500) prevents the lookup area from shifting as you drag the formula down the column.

Solution 2

Use the XLOOKUP Function (Newer Versions)

A modern and flexible alternative to VLOOKUP that allows you to return data from any column without requiring the identifier to be in the first column.

Powerful Data Processing Alternative

Effortlessly Cross-Reference Data with WPS Spreadsheet

WPS Spreadsheet fully supports VLOOKUP, XLOOKUP, and INDEX/MATCH formulas out of the box, allowing you to instantly match and consolidate datasets across massive workbooks without performance lags.

1. Open your datasets: Launch WPS Spreadsheet and open the workbook containing your disparate data sheets.
2. Insert the Lookup formula: Navigate to the Formulas tab, select 'Insert Function', and search for VLOOKUP or XLOOKUP to easily configure your lookup parameters using the interactive dialog box.
3. Populate cross-sheet data: Confirm the function arguments and drag the fill handle to automatically match and copy data between your worksheets in seconds.

100% compatible with Microsoft Excel (.xlsx) file formats and functionsNative support for advanced lookup operations like VLOOKUP and XLOOKUPLightning-fast processing speeds, even when handling cross-sheet references in large datasetsBuilt-in data cleanup tools to remove trailing spaces that disrupt lookup formulas

Frequently Asked Questions

Why is my VLOOKUP formula returning an #N/A error?

The #N/A error means the exact lookup value cannot be found in the reference array. This often happens if there are hidden trailing spaces, mismatched data types (like numbers formatted as text), or if the item simply doesn't exist on the second sheet.

Does the lookup column have to be the first column in the table?

If you are using VLOOKUP, yes. The function only searches the first column of the specified range. If your lookup value is in a middle column, you should use XLOOKUP or an INDEX and MATCH combination instead.

Can I pull data from a completely separate workbook?

Yes. You can reference external workbooks in your VLOOKUP or XLOOKUP formulas. Keep both workbooks open while writing the formula so you can simply click the external columns, and the software will automatically generate the correct file path syntax.

Excel & Data Problems