Function Problems

How to Match Data Between Excel Worksheets using XLOOKUP

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to match addresses in one Excel worksheet with addresses in another worksheet and return the corresponding data into a target range.

Product: Excel
Device & OS: not provided
Scenario: Pulling related values from a raw data sheet into a summary table based on matching addresses and column headers.
Observed behavior: To successfully populate the target cells with corresponding data from another worksheet using the XLOOKUP function.

Before you start

Ensure both your target worksheet and your source data worksheet are open. Verify that the address column used for matching does not contain leading or trailing spaces to avoid '#N/A' errors.

Solution 1Recommended

Use a Standard XLOOKUP to Return Multiple Adjacent Columns

Best when the return columns in the source data perfectly match the order needed in your target table.

By expanding the return array across multiple columns, a single XLOOKUP formula can pull in several fields of data simultaneously.

Select the target cell

Click on the starting cell (e.g., Table!E16) in your target worksheet where you want the matched data to appear.

Enter the XLOOKUP formula

Type the formula: `=XLOOKUP(B16,Raw_Dt!$I$16:$I$34,Raw_Dt!$J$16:$N$34,"Not Found")`.

Apply formula down the column

Press Enter to return the data array, then drag the fill handle at the bottom-right corner of the cell down to apply the formula to the remaining rows.

Understanding Absolute References: Using dollar signs ($) locks the lookup and return arrays so they do not shift downward when you copy the formula down.

Solution 2

Use Nested XLOOKUPs for Dynamic 2D Matching

Ideal if you need to match both the row (address) and the column headers simultaneously to return specific values.

Efficient Data Management with WPS Spreadsheet

Master Advanced Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like XLOOKUP, VLOOKUP, and nested formulas to seamlessly match data across multiple sheets. Its robust calculation engine makes handling large datasets easy and fast.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple worksheets.
2. Trigger the formula: Type '=XLOOKUP' in the formula bar to trigger the function syntax hints.
3. Select data ranges: Highlight your lookup value, switch to the second sheet to select your lookup array, and then select the return array.
4. Complete matching: Press Enter to execute the function and drag the fill handle to apply it across your dataset.

100% compatible with Microsoft Excel formulas (.xlsx)High-performance processing for matching large datasetsBuilt-in function autocomplete for error-free formula writingFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP return #N/A when matching across sheets?

This usually happens if the lookup value doesn't exactly match the source data. Check for hidden spaces, text-to-number format mismatches, or ensure your lookup arrays cover the correct ranges.

Can I use XLOOKUP to return data from another workbook entirely?

Yes, XLOOKUP can reference external workbooks. However, the external workbook must be open, or the file path must be fully specified in the formula for it to update properly without breaking.

What is the advantage of XLOOKUP over VLOOKUP for matching worksheets?

XLOOKUP defaults to an exact match, can search from right to left, and allows you to specify a 'Not Found' message natively. It also seamlessly returns an array of multiple columns without needing the COLUMNS function or matching column index numbers.

How do I fix a #SPILL! error when using XLOOKUP?

A #SPILL! error occurs when XLOOKUP tries to return multiple columns or rows, but the adjacent cells in the target range are not empty. Clear the blocking cells to allow the formula array to expand properly.

Excel & Data Problems