Learn Connecting Tables with XLOOKUP | Advanced Lookup Systems and Relational Modeling

Swipe to show menu

The workbook now contains multiple connected datasets. In this chapter, use XLOOKUP to connect tables dynamically and build calculated business metrics without duplicating data.

XLOOKUP Structure

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

lookup_value: value being searched;
lookup_array: column Excel searches through;
return_array: column returning the result;
[if_not_found]: optional fallback value.

Formula used in this chapter:

=XLOOKUP([@Product], Products[Product], Products[Cost])

[@Product]: current row product value;
Products[Product]: lookup column;
Products[Cost]: returned cost value.

Structured Table References

[@Product]

@: current row context inside an Excel Table.

Structured references automatically expand together with the dataset.

Step 1 Add the Cost Column

Inside Sales_Data, create a new column named:

Cost

Inside the first Cost cell, type:

=XLOOKUP([@Product], Products[Product], Products[Cost])

Press Enter.

Each product now retrieves its unit cost dynamically from the Products table.

Step 2 Test Dynamic Updates

Modify a product cost inside the Products table.

Confirm that all related rows inside Sales_Data update automatically.

Step 3 Test Missing Products

Enter a product that does not exist inside the Products table.

Notice that the lookup returns an error, signaling a missing relationship between the tables.

Step 4 Expand the Product Catalog

Add the following rows into the Products table:

Gaming Chair,Gaming,180,350
Gaming Headset,Gaming,45,120
Gaming Mouse,Gaming,30,80
Gaming Keyboard,Gaming,50,130
Gaming Controller,Gaming,35,90
Gaming Mousepad,Gaming,10,30
Gaming Webcam,Gaming,60,150
Gaming Microphone,Gaming,70,180
Capture Card,Gaming,80,200
Gaming Monitor,Gaming,220,480
Desk Lamp,Home Office,15,45
Webcam,Home Office,55,140
USB Hub,Home Office,20,55
Desk Organizer,Home Office,12,35
Monitor Stand,Home Office,25,70
Ergonomic Mouse,Home Office,35,90
Wrist Rest,Home Office,8,25
Cable Management Kit,Home Office,10,30
Desk Mat,Home Office,18,50
Laptop Stand,Home Office,30,75

The lookup formulas automatically support the new products because the source data is structured as an Excel Table.

Step 5 Add New Sales Data

Copy the following rows into Sales_Data:

15/03/2026,1074,C003,Gaming Chair,North,Ana,2
15/03/2026,1075,C007,Gaming Headset,South,Bruno,4
16/03/2026,1076,C012,Desk Lamp,East,Carla,3
16/03/2026,1077,C018,Gaming Mouse,West,Diego,6
17/03/2026,1078,C022,Laptop,Central,Elena,2
17/03/2026,1079,C031,Desk Organizer,Southwest,Felipe,8
18/03/2026,1080,C005,Gaming Keyboard,North,Gabriela,3
18/03/2026,1081,C014,Monitor Stand,South,Hugo,2
19/03/2026,1082,C028,Gaming Controller,East,Marcos,5
19/03/2026,1083,C041,USB Hub,West,Priya,4
20/03/2026,1084,C009,Gaming Mousepad,Central,Fatima,7
20/03/2026,1085,C033,Webcam,Southwest,Ana,2
21/03/2026,1086,C047,Phone,North,Bruno,3
21/03/2026,1087,C016,Gaming Webcam,South,Carla,1
22/03/2026,1088,C002,Ergonomic Mouse,East,Diego,4
22/03/2026,1089,C025,Keyboard,West,Elena,5
23/03/2026,1090,C038,Wrist Rest,Central,Felipe,6
23/03/2026,1091,C011,Gaming Microphone,Southwest,Gabriela,2
24/03/2026,1092,C044,Cable Management Kit,North,Hugo,10
24/03/2026,1093,C019,Tablet,South,Marcos,2
25/03/2026,1094,C006,Capture Card,East,Priya,1
25/03/2026,1095,C030,Laptop,West,Fatima,3
26/03/2026,1096,C048,Desk Mat,Central,Ana,4
26/03/2026,1097,C013,Gaming Monitor,Southwest,Bruno,2
27/03/2026,1098,C035,Laptop Stand,North,Carla,3
27/03/2026,1099,C021,Mouse,South,Diego,8
28/03/2026,1100,C042,Gaming Chair,East,Elena,1
28/03/2026,1101,C004,Monitor,West,Felipe,2
29/03/2026,1102,C037,Gaming Headset,Central,Gabriela,3
29/03/2026,1103,C050,USB Hub,Southwest,Hugo,5
30/03/2026,1104,C008,Laptop,North,Marcos,4
30/03/2026,1105,C026,Desk Lamp,South,Priya,6
31/03/2026,1106,C015,Gaming Controller,East,Fatima,3
31/03/2026,1107,C039,Monitor Stand,West,Ana,2
01/04/2026,1108,C023,Phone,Central,Bruno,5
01/04/2026,1109,C046,Gaming Keyboard,Southwest,Carla,4
02/04/2026,1110,C001,Wrist Rest,North,Diego,7
02/04/2026,1111,C034,Webcam,South,Elena,2
03/04/2026,1112,C017,Gaming Mousepad,East,Felipe,5
03/04/2026,1113,C029,Desk Mat,West,Gabriela,3

Step 6 Build the Profit Formula

Replace the intermediate logic with:

=[@Revenue]-(XLOOKUP([@Product],Products[Product],Products[Cost],"Product not in system")*[@Units])

[@Revenue]: current row Revenue;
XLOOKUP(...): retrieves unit cost;
[@Units]: quantity sold;
"Product not in system": fallback message for missing products.

Step 7 Validate the System

Modify both Products and Sales_Data.

Confirm that:

Cost values update automatically;
Profit calculations update automatically;
New rows inherit all formulas automatically.

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 3. Chapter 1

Connecting Tables with XLOOKUP

XLOOKUP Structure

Structured Table References

1. Why is `XLOOKUP` preferred in relational Excel models?

2. What does the `@` symbol represent inside Excel tables?

3. Why is data duplication discouraged in relational spreadsheet models?

Connecting Tables with XLOOKUP

XLOOKUP Structure

Structured Table References

1. Why is XLOOKUP preferred in relational Excel models?

2. What does the @ symbol represent inside Excel tables?

3. Why is data duplication discouraged in relational spreadsheet models?

1. Why is `XLOOKUP` preferred in relational Excel models?

2. What does the `@` symbol represent inside Excel tables?