Excel Magic Trick 1085

DESCRIPTION

What if you are not allowed to have a helper columns to lookup Price and Region with VLOOKUP? And you need to calculate Total Revenue by Region?

This video shows how to create a “Relational Database like” Array formula to calculate Revenue by Region when the fact table (transaction table) has only Product Name, Units Sold and Sales Rep and you have a lookup table to get Product Price and a second lookup table for Sale Rep’s Region.

This video shows how to create a PowerPivot-like formula without PowerPivot!

(00:08 min) Look at Helper columns to solve this.
(02:10 min) Simulate VLOOKUP Helper Column In Array Formula for Number using SUMIF function
(07:23 min) Simulate VLOOKUP Helper Column In Array Formula for Text using LOOKUP function
(09:03 min) Total Formula for Revenue by Region

Lookup Adding. SUMPRODUCT Lookup Adding. Simulate Lookup Helper Column in Array Formula. Helper Column in Formula. Simulate PowerPivot Formula without PowerPivot

WORKBOOKS