Function Problems

How to Merge Excel Tables and Add Matching Values Using XLOOKUP

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to match nationality names across two separate Excel tables, sum their corresponding values, and return zero if a country is missing from the second table.

Product: Excel
Device & OS: not provided
Scenario: Merging data from two tables based on matching text criteria and calculating the total sum of numerical values.
Observed behavior: The goal is to calculate the combined total values per category across two tables utilizing XLOOKUP and structured table references without returning errors for unmatched items.

Before you start

Ensure both of your datasets are formatted as formal Excel Tables (Insert > Table) and verify their specific Table Names (e.g., Table1, Table2) from the Table Design tab before applying the formula.

Solution 1Recommended

Use XLOOKUP and Table References to Add Values

Use the XLOOKUP function combined with basic addition to match rows across tables and sum their values, utilizing the built-in error handling to return a 0 when no match is found.

By structuring your data as official tables, formulas dynamically adjust to new rows. The XLOOKUP function simplifies merging this data compared to older functions like VLOOKUP by offering a native 'if not found' argument.

Add a new column

In your first table, click on the header of the column to the right of your existing data and type a new name, such as 'Total'.

Enter the XLOOKUP formula

In the first data row of the newly created 'Total' column, enter the formula: =[@Number of People]+XLOOKUP([@Nationality],Table2[Nationality],Table2[Number of People],0).

Customize the table references

Replace 'Table2', 'Number of People', and 'Nationality' in the formula to match the actual table name and column headers used in your specific workbook.

Handling New Unmatched Data: If the second table contains new countries that are not present in the first table, they will not appear in your final list. You should append or combine the tables first using Power Query or manually copy the unique names before running this formula.

Seamless Data Processing

Merge Tables and Use Advanced Functions Effortlessly in WPS Office

WPS Spreadsheet fully supports advanced functions like XLOOKUP and structured table references, allowing you to merge datasets, perform complex calculations, and analyze your data seamlessly.

1. Format data as tables: Select your data ranges in WPS Spreadsheet and press Ctrl+T to convert them into structured tables.
2. Add a calculation column: Create a new column in your primary table to hold the merged totals.
3. Apply XLOOKUP: Input the XLOOKUP formula to fetch and add the numerical values from your secondary table automatically.

Fully compatible with Microsoft Excel formulas, including XLOOKUP and dynamic arrays.Lightweight software that handles large datasets and tables with ease.Built-in advanced data analysis and table formatting tools.Completely free and easy to use across Windows, Mac, and mobile devices.

Frequently Asked Questions

What does the '0' at the end of the XLOOKUP formula mean?

The '0' represents the [if_not_found] argument in the XLOOKUP function. It tells the software to return a value of 0 instead of an #N/A error if the lookup value (like a specific nationality) does not exist in the second table.

Why is my formula returning a #NAME? error?

The #NAME? error usually occurs if your current version of spreadsheet software does not support the XLOOKUP function, or if the function name is misspelled. Make sure your software is updated to the latest version.

Can I use VLOOKUP instead of XLOOKUP for merging tables?

Yes, you can use VLOOKUP combined with the IFERROR function for a similar result. For example: =[@Number of People]+IFERROR(VLOOKUP([@Nationality],Table2,2,FALSE),0). However, XLOOKUP is recommended as it is faster and has built-in error handling.

Why is my structured table reference formula showing a #REF! error?

A #REF! error in structured references typically means the table name or the column header names referenced in the formula do not match the actual names in your workbook. Check the Table Design tab to verify your exact table names.

Excel & Data Problems