Stop Using VLOOKUP for Reports (Do This Instead)

About this video

Kevin Stratvert argues VLOOKUP/XLOOKUP are the wrong tool for recurring Excel reports because copying lookup formulas duplicates data, slows files, and forces rebuilding when new data arrives. Instead, keep tables separate, define relationships, and analyze with one PivotTable that updates on Refresh. Using Kevin Cookie Company sales data, he converts Sales, Products, and Stores ranges into named Excel Tables with Ctrl+T, adds all three to the Data Model via Power Pivot (enabling the Data Analysis add-in), and opens Diagram View. He drags Store ID between Sales and Stores and Product ID between Sales and Products to establish relationships without copying data. He then inserts a PivotTable from Data Model, placing Product Name in Rows, Region in Columns, and Quantity in Values. After appending new rows to Sales, a single Refresh updates the report. For calculations, he creates a Power Pivot Measure named Revenue using SUMX to multiply Quantity by the related Unit Price from Products, formatted as currency, so revenue calculates only where the PivotTable needs it rather than in thousands of sheet formulas.

Key takeaways

• Copying VLOOKUP/XLOOKUP down a report duplicates data, slows the file, and makes recurring reports hard to maintain
• Convert each range to a named Excel Table (Ctrl+T), then add Sales, Products, and Stores to the Power Pivot Data Model
• Define relationships in Diagram View by dragging matching fields like Store ID and Product ID between tables
• Build one PivotTable from the Data Model and update the whole report by adding rows and clicking Refresh
• Create calculations as Measures — e.g., Revenue with SUMX and RELATED — so they live in the PivotTable, not in sheet columns

Episode details: published January 30, 2026 · 11:38 long. Summary written by Self Growth Videos from the episode itself. Business results and any income figures mentioned are Kevin Stratvert’s own claims, shared for education — they are not typical or guaranteed.

← Previous: Build an App. Win $50K | The Base44 Big Game Challenge · Next: UiPath on macOS: Automate Your Mac with Studio Web →

All Kevin Stratvert videos · Kevin Stratvert profile · Kevin Stratvert on YouTube