Microsoft Excel

A Smarter Way to Link Excel Tables Without XLOOKUP or VLOOKUP

Chris Menard

Chris Menard

— 2 min read
A Smarter Way to Link Excel Tables Without XLOOKUP or VLOOKUP

Most people use XLOOKUP or VLOOKUP to combine data from two Excel tables. But there's a smarter approach: Excel's built-in Data Model and table relationships. This method links tables like a database — no formulas, no duplicate columns, and no maintenance when data changes. In this tutorial, Chris Menard walks through the entire process.

Watch the full tutorial: Smarter Way to Link Excel Tables (Without XLOOKUP or VLOOKUP or Duplicates)

The Problem with Lookup Formulas

When you use XLOOKUP or VLOOKUP to pull data from another table, you're creating duplicate columns in your dataset. If the source data changes, the formulas need to recalculate. With thousands of rows, this slows down your workbook and creates maintenance overhead.

Setting Up the Tables

For this example, Chris uses two tables:

  • Customers table — Customer ID, Name, City, State
  • Orders table — Order ID, Customer ID, Product, Total Amount

Both tables share a common field: Customer ID. This is the key that links them together — just like a primary key in a database.

Excel worksheet showing a Customers table with columns for Customer ID, Name, City, and State
The Customers table — one of two tables that will be linked using a Data Model relationship.

Make sure both datasets are formatted as Excel Tables (select the data and press Ctrl+T). Give each table a meaningful name in the Table Design tab.

Adding Tables to the Data Model

  1. Click inside one of the tables
  2. Go to Insert > PivotTable
  3. Check the option "Add this data to the Data Model"
  4. Click OK to create the PivotTable

In the PivotTable Fields panel, click "All" (instead of "Active") to see both tables listed. Now you can drag fields from both tables into the PivotTable.

Creating the Relationship

When you add fields from both tables, Excel shows a yellow warning: "Relationships between tables may be needed." Click the CREATE button.

PivotTable Fields panel showing a yellow warning banner saying Relationships between tables may be needed with a CREATE button
Excel detects that you're using fields from multiple tables and prompts you to create a relationship.

In the Create Relationship dialog:

  • Table: Orders (the table with the foreign key)
  • Column: Customer ID
  • Related Table: Customers
  • Related Column: Customer ID

Click OK, and Excel links the two tables. No formulas, no extra columns — the relationship lives in the Data Model.

The Result

Now your PivotTable can show fields from both tables together. You can display the customer name, city, and state alongside order details and totals — all without a single lookup formula. The PivotTable refreshes automatically when either table's data changes.

PivotTable showing combined data from both tables with Order ID, Customer ID, Product, Name, and Sum of Total Amount
The final PivotTable pulls fields from both the Orders and Customers tables — no lookup formulas needed.

This approach is also the foundation for combining multiple tables with relationships, which scales to 3, 4, or more linked tables — something that becomes impractical with nested lookup formulas.

Want to learn more? Visit courses.chrismenardtraining.com for online training courses.

Read more