Learn Creating a Relational Model | Data Foundations and Excel Essentials

Swipe to show menu

Right now, product pricing lives directly inside the Revenue calculation. In this chapter, move pricing into a separate table and connect both sheets using XLOOKUP.

Model Structure

Sales_Data: transaction data;
Products: product pricing data;
Product prices should live in a separate lookup table;
Pricing updates should happen in one central location.

This structure creates a single source of truth for product pricing.

XLOOKUP Structure

=XLOOKUP(lookup_value, lookup_array, return_array)

lookup_value: value being searched;
lookup_array: where Excel searches;
return_array: value returned from the matching row.

Note

Formula argument separators in Excel may vary depending on your regional settings and Excel localization. Some versions use commas , while others use semicolons ;. If a formula does not work correctly, try replacing the separator accordingly.

Step 1 Create the Products Sheet

Create a new worksheet named:

Products

Add the following columns:

Product
Category
Cost
Price

Step 2 Add Product Data

Product	Category	Cost	Price
Laptop	Tech	900	1500
Monitor	Tech	240	400
Keyboard	Tech	70	120
Mouse	Tech	25	40
Phone	Tech	480	800
Tablet	Tech	360	600

Step 3 Convert the Dataset into an Excel Table

Select the dataset and press:

Ctrl + T

Confirm the table contains headers.

Step 4 Create the First Lookup

Return to the Sales_Data sheet.

Inside H2, type:

=XLOOKUP(D2, Products!A:A, Products!D:D)

D2: product name;
Products!A:A: lookup column;
Products!D:D: return column.

Press Enter.

Step 5 Test the Connection

Change one of the prices inside the Products table.

Notice that the value updates automatically inside Sales_Data.

Step 6 Build the Revenue Formula

Replace the previous formula with:

=XLOOKUP(D2, Products!A:A, Products!D:D) * G2

XLOOKUP(...): retrieves the product price;
G2: units value.

Step 7 Clean the Dataset

Delete the temporary lookup-only column if needed. Keep only the final Revenue column.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 2

Ask AI

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

Section 1. Chapter 2

Creating a Relational Model

Model Structure

XLOOKUP Structure

1. Why is product pricing stored in a separate Products table?

2. What does `Products!D:D` represent?

3. Why is Revenue calculated using `XLOOKUP` instead of storing Price directly inside `Sales_Data`?

Creating a Relational Model

Model Structure

XLOOKUP Structure

1. Why is product pricing stored in a separate Products table?

2. What does Products!D:D represent?

3. Why is Revenue calculated using XLOOKUP instead of storing Price directly inside Sales_Data?

2. What does `Products!D:D` represent?

3. Why is Revenue calculated using `XLOOKUP` instead of storing Price directly inside `Sales_Data`?