Function Problems

How to Import Excel Data from Another Sheet Using Matching Names

Kushani Nimanthika Oct 23, 2026 868 views

Question details

Retrieve specific data values from one worksheet into another worksheet automatically based on matching criteria, such as a company name.

How to Import Excel Data from Another Sheet Using Matching Names

Product: Spreadsheet
Device & OS: not provided
Scenario: Merging data sets from two different time periods (like 2020 and 2021) by linking the data through a common identifier column.
Observed behavior: Needing a scalable formula or tool to fetch secondary sheet data without manual copying and pasting, maintaining data accuracy.

Before you start

Ensure that the company name columns in both worksheets are formatted as text and do not contain trailing spaces, as these will prevent exact matches from being found.

Solution 1Recommended

Use XLOOKUP to Fetch Matching Data

XLOOKUP is the most modern and robust function to retrieve data from another sheet using a matching identifier.

XLOOKUP simplifies cross-referencing by letting you specify exactly what column to look in and what column to return data from. Unlike older formulas, it handles both exact and approximate matches seamlessly without needing an index number.

Select the Destination Cell

Select the cell in your target sheet (e.g., the 2021 sheet) where you want the imported data to appear.

Initiate the Formula

Type =XLOOKUP( and click on the cell containing the company name you want to match.

Define the Lookup Array

Navigate to the source sheet (e.g., the 2020 sheet) and select the entire column containing the company names.

Define the Return Array

Select the column in the source sheet containing the data you want to retrieve, close the parenthesis ), and press Enter.

Drag to Apply: Once the first row returns the correct data, double-click the fill handle in the bottom-right corner of the cell to apply the formula down the entire column.

Solution 2

Use VLOOKUP for Traditional Data Matching

If you are using an older spreadsheet version, VLOOKUP is a reliable standard for finding matching data.

Solution 3

Merge Tables Using Power Query

For large datasets or continuous data pipelines, merging tables via Power Query provides a more stable, formula-free approach.

Efficient Spreadsheet Management

Retrieve Matching Data Instantly with WPS Spreadsheet

WPS Office provides comprehensive support for powerful lookup formulas like VLOOKUP, XLOOKUP, and INDEX/MATCH, making it effortless to cross-reference and import data across multiple sheets.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your multiple worksheets.
2. Insert the Lookup Function: Click the Formulas tab and select 'Insert Function' to find XLOOKUP or VLOOKUP.
3. Configure the Formula Settings: Follow the built-in wizard to seamlessly select your lookup value, source array, and return array.
4. Apply Across Your Dataset: Click OK and drag the formula down to instantly pull all matching data points across your sheets.

Full compatibility with Microsoft Excel formulas and file formats (.xlsx).Intuitive formula builder to assist with complex lookups without syntax errors.Free, lightweight alternative that runs smoothly even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning a #N/A error when the company names look identical?

This usually happens due to hidden spaces, different text formatting, or typos. Use the TRIM() function on both columns to remove extra spaces and ensure the names are properly matched.

Can I use INDEX and MATCH instead of VLOOKUP?

Yes. INDEX and MATCH is highly flexible and allows you to look up values to the left of your matching column, bypassing VLOOKUP's limitation of only searching left-to-right.

Is XLOOKUP available in all spreadsheet software versions?

XLOOKUP is available in newer software versions like Microsoft 365, Excel 2021, and updated versions of WPS Office. If you are using much older software, you will need to rely on VLOOKUP or INDEX/MATCH.

Does Power Query update automatically if the source data changes?

Power Query requires a refresh to pull in new data. You can right-click the merged query table and select 'Refresh', or configure the connection properties to refresh the data automatically when the file opens.

Excel & Data Problems