Spreadsheet models can become error prone, labour intensive and unable to scale if they are allowed to grow un-checked. A culprit is the VLOOKUP formula.
LOOKUP allows authors to connect and cross-reference between tables of values from one area or document to another.
Workbooks that are reliant upon VLOOKUP formulae can be re-structured to more cleanly combine sets of data using Power BI instead.
VLOOKUP can be eliminated by Power BI using PowerPivot – which is the Excel feature that organizes your information separately within a data model having relationship linkages that are set up once and remain fully established regardless of data updates that may come through over time.
So rather than using VLOOKUP to connect up independent Excel tables the PowerPivot feature places these tables into an Excel data model, or ‘Cube’. Now the relationships are managed properly within the ‘Cube’. This avoids the error situations often encountered using VLOOKUP where spreadsheet cell values are extended or updated but then lookup connections fail.
How then can the values be accessed? Cube values can now be easily be referenced from a spreadsheet formula function called =CubeValue(). Alternatively Pivot Tables can access the Cube.
So there you have it; no more VLOOKUPs!
Why is this better? A Power BI approach creates a scalable solution that can handle tens of millions of rows with ease. It’s also the cleaner approach because you are segregating your data from your calculations.
In a future blog I’ll provide some technical details in a worked example.
Until then; Power on!