If you keep copying lookup formulas between tables and fixing them when source data changes, Excel’s Data Model offers another approach: relate the tables once, then use their fields together in a PivotTable. It is built into the workbook—not a separate database server—and it can reduce the need to maintain repeated lookup results. It does not make lookup formulas obsolete; the right choice depends on what you need to produce.
What Excel’s “hidden relational database” actually is
The feature is the Excel Data Model: a collection of related tables stored in a workbook. Instead of copying a matched value into every row of a second table, you define how the tables connect. A PivotTable can then use fields from both.
As an Amazon Associate I earn from qualifying purchases.
Microsoft describes the distinction this way: “Unlike lookup formulas, relationships do not duplicate data. Instead, they link tables so that fields from each table can be used together in a PivotTable.” (Microsoft Support: Create a relationship between tables in Excel.)
Free tools Windows power users keep installed
One-click scans. No signup required.
For example, an Orders table can keep each order’s CustomerID while a Customers table stores customer names and regions. Rather than adding a formula-filled customer-name column to Orders, relate the tables and place customer fields alongside order fields in a PivotTable.
When a relationship can replace a lookup formula
Use a relationship when the goal is to analyze or report across tables—for example, summarizing orders by customer name, region, or another field held in Customers. Once the tables are related, add the relevant fields from each table to the PivotTable instead of materializing the same lookup result down a column.
This changes how the report gets its fields; it does not automatically create a permanent copied value in the source table. If another process expects a flattened table with a customer name on every order row, a relationship alone may not meet that requirement.
Rank #2
- Used Book in Good Condition
How to set up the Data Model
- Prepare the source tables. Make each data range an Excel table, with a clear header row and a key column that identifies records. In the example, Customers has CustomerID and Orders has CustomerID.
- Load both tables into the model. Use Excel’s Get Data/import workflow to bring the tables into the workbook Data Model. Microsoft’s setup guidance describes importing tables and creating relationships as part of this workflow: Create a Data Model in Excel.
- Create the relationship. Open the Data Model or Power Pivot relationship view available in your Excel edition, then connect Customers[CustomerID] to Orders[CustomerID]. The Customers key should be unique; the matching Orders key can recur for multiple orders.
- Build a PivotTable from the model. Insert a PivotTable using the workbook’s Data Model, then select fields from both tables—for instance, Customers[Name] and Orders[OrderTotal].
- Check the result. Confirm that the relationship uses the intended key columns and that totals and categories match what the underlying data supports.
Microsoft lists Data Model creation guidance for Microsoft 365, Excel 2024, and Excel 2021; related Power Pivot guidance also covers Excel 2019 and 2016. Exact menus and feature availability vary by edition, so the labels in your installation may differ. See Microsoft’s Power Pivot overview and learning page.
Choose a relationship or a DAX lookup for the job
A relationship is often enough when a PivotTable needs fields from connected tables. If a formula inside the model needs to return or work with related data, DAX provides functions with different purposes:
RELATEDreturns one value from a related table for the current row, making it suitable for a calculated column when that row needs a related value.RELATEDTABLEreturns the rows related to the current row, which can be used in patterns that aggregate related records.LOOKUPVALUEsearches for a value using specified criteria, useful when the desired match is not simply a traversal of an existing relationship.
These are Data Model formulas, not interchangeable spellings of worksheet VLOOKUP. Their results depend on the model’s tables, relationships, filters, and evaluation context. Be explicit about whether a calculation is a calculated column or a measure; they are evaluated differently. Microsoft explains the available lookup approaches in Lookups in Power Pivot formulas and VLOOKUP functionality in Power Pivot.
Check these relationship limits before modeling
- The lookup-side key must be unique. A relationship that uses a lookup column needs unique values on that side; duplicate IDs can prevent the intended one-to-many setup.
- Matching columns need compatible data types. A number stored as text in one table and as a number in another may not behave as a valid matching key.
- Composite keys need preparation. The model does not use a multi-column composite key directly as one relationship. Combine the key components into a single column in each table first, making sure the combined values remain unambiguous.
- Many-to-many cases need deliberate modeling. The documented relationship model does not provide a native many-to-many relationship; Microsoft describes using DAX to model such cases.
- Loops and self-joins have restrictions. Do not assume every circular relationship or relationship from a table to itself is valid.
- Automatic detection is only a suggestion. Excel can infer possible relationships from column metadata and value statistics, but that does not establish that the proposed keys represent the correct business relationship. Inspect the columns and verify uniqueness before relying on an automatically created link.
Microsoft’s relationship guidance covers these constraints and the model’s key concepts: Relationships between tables in a Data Model.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When to keep a lookup formula
Keep a worksheet lookup when the result must be written into a regular cell for downstream work, the data is not being analyzed through a Data Model PivotTable, or the model’s relationship constraints do not fit the source structure. For report-building across cleanly keyed tables, a relationship can avoid maintaining repeated copied results. Treat it as a modeling choice, not a universal replacement for VLOOKUP or other lookup formulas.
Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
