Link Tables in Excel: XLOOKUP, Data Model & More

Why does your formula return #N/A when the data looks identical in both tables? That's the question that pulled me into three hours of debugging on a Tuesday afternoon about twelve years ago, and the answer, embarrassingly, was a trailing space. Linking tables in Excel sounds simple until a formula fails silently, a presentation goes sideways, or you realize you've been copying data by hand every week for months. A colleague of mine did exactly that: three hours every Friday, manually moving data between sheets. I built her a VLOOKUP and she had it done in thirty seconds. That kind of time savings is what learning to properly link tables in Excel actually buys you.

There are three real paths here: formula-based linking with XLOOKUP (or INDEX/MATCH), relationship-based linking through the Data Model, and cross-sheet or cross-workbook references. Which one fits depends on your data structure and what you're building. This article walks through all three so you can pick the right one before you write a single formula.

What You Can Link in Excel and Which Method Actually Fits Your Data

Named Tables vs. Plain Ranges: Why the Distinction Matters Before You Start

If your data isn't formatted as a named table (Insert → Table, or Ctrl+T), you're working with a plain range, and plain ranges are fragile. They don't expand automatically when rows are added, they use cell addresses that break when columns shift, and they make cross-table formulas significantly harder to audit. Dynamic table linking in Excel depends on named tables. Before following any step below, format both datasets as tables and give each one a meaningful name in the Table Design tab. EmployeeTable is readable. Table3 is not.

A primary key (a column with unique, non-blank values in each table) is the other prerequisite. Every linking method in working with Excel tables and ranges relies on one. Without it, your link is a guess.

---

Step 1: Link Two Tables in Excel Using XLOOKUP (The Modern Formula Method)

Once your tables are named and your shared column is clean, the formula method is the fastest way to start pulling data across tables. XLOOKUP is what I'd use for almost any formula-based link in Microsoft 365. Here's a concrete example: you have OrdersTable with a CustomerID column, and CustomerTable with CustomerID and CustomerName. To pull the name into the orders table:

=XLOOKUP([@CustomerID], CustomerTable[CustomerID], CustomerTable[CustomerName])

In other words, find this row's customer ID in the customer table, then return the name sitting next to it.

When VLOOKUP Falls Short and Why XLOOKUP Fixes It

I resisted switching from VLOOKUP longer than I should have. What finally did it was a project where the column I needed was to the left of my lookup column. VLOOKUP can't do that: it only looks right. I spent an hour restructuring the table before admitting the tool was wrong for the job. That doesn't happen with XLOOKUP.

The other VLOOKUP problem is the hardcoded column number. =VLOOKUP(A2, CustomerTable, 3, FALSE) breaks silently if someone inserts a column. XLOOKUP references the column by name, so the structure can change without breaking the link. For merging tables in Excel without Power Query on Microsoft 365, XLOOKUP is the right call. If you're on Excel 2016 or 2019, stick with VLOOKUP or INDEX/MATCH, since XLOOKUP isn't available, and compatibility beats modernity for most readers.

One rule worth following without exception: wrap your lookup value in TRIM before trusting any result. =XLOOKUP(TRIM([@CustomerID]), CustomerTable[CustomerID], CustomerTable[CustomerName]). Trailing spaces are the single most common reason a link two tables in Excel with common column formula returns #N/A when the data looks correct. Two seconds of TRIM saves hours of confusion.

---

Step 2: Create a Table Relationship in Excel Using the Data Model

Formula-based linking works well for adding columns. But if you need to build a PivotTable that draws from two separate tables simultaneously, without VLOOKUP merging, the Data Model is a different and better tool for that job.

To create an Excel data model table relationship, follow these steps:

1. Go to the Power Pivot tab and select Add to Data Model for each named table.
2. In Power Pivot, open Diagram View and drag the primary key column from one table to the matching foreign key column in the other. That defines the relationship.
3. Back in Excel, insert a PivotTable and choose "Use this workbook's Data Model." Both tables are now available as one source.

When Not to Use Power Pivot (The Grouping Limitation Nobody Mentions)

Here's the part most tutorials skip: when you build a PivotTable from the Data Model, you lose the ability to group date fields the normal way. Right-click → Group is grayed out. If your report needs quarterly or monthly grouping, the Data Model path will frustrate you. Use XLOOKUP to merge what you need into a single table first, then pivot from that.

The create relationships between tables in Excel approach through Power Pivot is worth learning, but go in knowing that limitation exists before you build a report around it.

---

Step 3: Link Tables Across Sheets or Workbooks Without Losing the Connection

The Data Model handles in-workbook relationships well. Cross-sheet and cross-workbook links are a different problem, and they're where a lot of people run into broken references.

For cross-sheet linking, structured table references work cleanly: =XLOOKUP([@ID], Sheet2!EmployeeTable[ID], Sheet2!EmployeeTable[Name]). The sheet name prefix keeps it stable as long as the sheet isn't renamed.

Cross-workbook links are trickier. A formula like =[Source.xlsx]Sheet1!EmployeeTable[Name] works while both files are open, but close the source file and Excel may prompt to update links or break them silently, depending on where the file is saved. I've seen this cause real problems in shared environments. The stable fix: use Power Query to import the source table rather than linking directly. Power Query connections survive saves, file moves, and closed workbooks in a way that direct formula links to external workbooks simply don't. For dynamic table linking in Excel across workbooks in 2026, Power Query is the approach I'd recommend if reliability matters.

If you're new to how tables behave structurally before adding cross-sheet links, the guide to structured references in tables is worth reading first, since it covers exactly how Excel interprets those TableName[ColumnName] references.

---

Common Mistakes When You Link Tables in Excel and How to Avoid Them

Three errors show up constantly, and all three are preventable.

Using plain ranges instead of named tables is the most common. When your source data is just a range, adding a row at the bottom doesn't expand the reference, and your link quietly misses new data. Format it as a table first, every time.

Mismatched data types on key columns cause silent failures. A primary key stored as text in one table and as a number in the other will never match, even if the values look identical. Check the column format before assuming the formula is broken.

IFERROR warning: Wrapping a linked formula in IFERROR before you've confirmed it works correctly is a mistake I've made and watched others make. It hides legitimate structural errors (deleted columns, renamed sheets, wrong table headers) and makes debugging almost impossible. Get the link working correctly first. Then add IFERROR as a finishing layer for genuinely expected misses.

If you want to go deeper on how tables and ranges behave before you start building links between them, the Excel beginner's guide covers the foundation in plain terms.

Format your data as named tables before you build any cross-table link. Everything else (XLOOKUP, the Data Model, cross-workbook references) works better and breaks less when the foundation is right.

---

Frequently Asked Questions