Skip to main content

Using INDIRECT to obtain a VLookup with a variable array reference

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!
Darrell Wolfe
Storyteller | Writer | Thinker | Consultant | Freelancer

Popular posts from this blog

Learning Coding Fundamentals with Python and SQL

Learning Coding Fundamentals with Python and SQL Learning Coding Fundamentals with Python and SQL Darrell Wolfe ————————————————————— Disclaimers First It is not my intention to steal anyone’s thunder or copyrighted material. I do not believe these seven fundamentals are specific to Dr Hill (below), who was the initial inspiration to start this note file. That beings said, she has a particularly unique method of teaching, and I strongly recommend that if you are someone who needs a good teacher, she’s the one! This is my own process of learning. I take information from as many sources and teachers as possible, synthesis that material, and then practice it until I get good at it. Further, I like to take detailed notes so I can refer back to them when a particular tool starts getting rusty or dusty in my brain after disuse for a time. When I learned .rmd through my Google Data Analytics Certification, I

I can't find my Blogger.com DNS record?! Here's how to find it. It took me a long time because Google's own instructions fail.

I use Blogger.com for my websites. I find it easier to use without having to know a lot of technical things.  However, I bought my domain names from a third-party website, and I host them on Blogger.com.  After years of this, I tried a hosted Word Press site, I found the GUI awful and editor even worse. I'm sure it has amazing features and it looked pretty, but it was absolutely useless to me as a writer. So... I went back to Blogger.com, but ran into an odd issue. I needed my personal DNS record to provide to my domain provider.  The Google instructions " Set up a custom domain " say that I should get a pop-up message with my DNS records. There is noplace in the blogger interface to find the DNS record that I can find, neither in the website or elsewhere. That is an odd user interface failure. Others expressed the same issue and even ChatGPT4o Pro couldn't help, it kept taking me back to these instructions.  Finally! I found the answer on this page: Why I'm not g

Lesson Plan: Acquiring a Business Using SBA 7(a) Loans - Week 1: Introduction and Preliminary Research

Week 1: Introduction and Preliminary Research Objectives: Understand the basics of SBA 7(a) loans. Identify the steps involved in acquiring a business. Activities: Research SBA 7(a) Loans: Read articles and guides on the SBA website about the 7(a) loan program. Watch educational videos on YouTube about SBA loans. Familiarize with the Business Acquisition Process: Review resources on SCORE.org about buying a business. Read case studies of successful business acquisitions. Resources: SBA.gov SCORE.org YouTube (search for SBA 7(a) loan tutorials) --------------------------------------------------------------------------------- Research SBA 7(a) Loans: To get started on acquiring a business using SBA 7(a) loans, you can follow these steps and use the available resources: Understanding SBA 7(a) Loans SBA 7(a) Loan Overview: The SBA 7(a) loan program is the SBA’s primary program for providing financial assistance to small businesses. These loans can be used for various purposes, including