Case Study for using INDIRECT to obtain a VLookup with a variable array reference
*For reference: I did not build this workbook, it came this way and I'm stuck with it until I can rebuild it correctly next year.*
The Problem:
I have a legacy workbook that I have to rebuild for next year, but his year I'm stuck with.
This workbook has at or near 100 tabs that need to be referenced on a Totals and Subtotals set of pages. There are a thousand better ways to do that, especially using Power Query and Power Pivot, but here we are.
These sub-totals pages were for specific sub-sets of the data that could be found on any given page (Ex: One sub-totals page looks for all properties with a PC Code "548").
On the sub-totals pages, the previous builder had simply linked the cells to the various pages; resulting in over a thousand rows of data that looked like this:
//='2001'!$A$20//
However, when the end-user filtered the data on the origin pages, the data on the sub-totals pages would break because it was hard coded to a specific cell. The data in that cell changed every time a filter was applied. Many of the rows were now showing data for non-548s.
Sub-Problem: I the end-user wants to be able to filter their data while doing analysis, as they should.
Solution Attempt 1
My first attempt to solve for this issue, was to rebuild the sub-totals with a VLookup formula.
//=VLOOKUP(B18,'2001'!B17:DD200,6,FALSE)//
While this was successful, it posed a new problem. Each row of data needs to reference a different Neighborhood (aka Geo). This resulted in my having to hard-code a new geo for each row every time that changed in the requested data set.
Solution Attempt 2
In order to allow the end-user to use Filters on the data sets, and not have to hard-code the variables into the formula, I had to have variable within the VLookup that could reference the over 100 sheet names.
Each sheet name is identical to the geo/NBHD number. After a quick chat with ChatGPT and a review of the syntax for "INDIRECT", I found a solution.
By using INDIRECT as the the array, I was able to feed the variable component into the VLookup formula. From that point, I only had to fix each column to reference the correct number of columns over in the array.
Results:
// =VLOOKUP($B18,INDIRECT("'"&$J18&"'!$B$17:$DD$500"),6,FALSE) //// =VLOOKUP($B18,INDIRECT("'"&$J18&"'!$B$17:$DD$500"),7,FALSE) //
// =VLOOKUP($B18,INDIRECT("'"&$J18&"'!$B$17:$DD$500"),32,FALSE) //
In this way, the entire sub-totals page could be fed the data it needs to reference, and the end-user was still able to use their filter.
PS -
The new version, for next year, will be built on Tables and the Sub-totals will use Table formulas which are inherently dynamic. Once a Table is created and named, it can be referred to by it's name and column.
A table formula would look like:
//=VLOOKUP($C6,G_2010,2,FALSE)//
or when by Table Name[Column Name]
//=COUNTA(G_2010[LRSN])//
PSS-
In the end, we hope to move to a combination of (1) SQL and Tableau and (2) onboard software we already own but was never set up correctly to do these analysis. However, there are database restrictions outside of our control that require fixing before we can implement those options. We are working to Dev that project out for year three or beyond.
Shalom שָׁלוֹם: Live Long and Prosper!
Shalom שָׁלוֹם: Live Long and Prosper!
Darrell Wolfe
Storyteller | Writer | Thinker | Consultant | Freelancer