If you work a lot in Excel, you’re probably familiar with VLOOKUP/HLOOKUP/XLOOKUP functionality which allows you to pull data from an array into your spreadsheet. This functionality is very powerful when working with large dataset on a single tab in a workbook - you simply define the array you want to reference and the column number you wish to return data from (i.e. VLOOKUP($B5,’Division A’!$B:$C,2,FALSE).
But what if that reference (‘Division A’!$B:$C) is variable? Perhaps you get similar datasets from multiple divisions, so you are presented with one tab for each division and you want to be able to quickly change among division’s tabs to review.
You could do a Find/Replace (Ctrl-H) to replace ‘Division A’ with ‘Division ‘B in the VLOOKUP formula each time you wish to change your view. But a better way is to use the INDIRECT function to make the reference array contingent upon the title of your column.
You just need to convert the reference string (‘Division A’!$C:$F) into a text string where ‘Division A’ is a variable. The INDIRECT function syntax is very simple (INDIRECT(ref text)) so we can just build a text string to emulate the reference string. Cutting to the chase, this is what that string will look like:
INDIRECT("'" & $C$4 & "'!$B:$C")
Where:
$C$4 is the text: Division A (for now)
The opening and closing single quotes are enclosed in double quotes (like any Excel text) along with the tab reference exclamation point and column references ($B:$C)
& symbol is used to connect multiple text strings
Then we plug that INDIRECT function into the VLOOKUP as follows:
VLOOKUP($B5,INDIRECT("'" & $C$4 & "'!$B:$C"),2,FALSE)
Now when we change the title of the column, the formulas update to pick up data from the correct tab.
Using the INDIRECT function, we can make every element of the a reference array variable - if need be. For example:
This is only a sample of how this function can be used.
Always remember: Excel Can Do Anything. You just need to think through what you need the tool to do and how to break it down into logical chunks. If you have an Excel brain teaser I can help with, please email me at: matt@redbladesolutions.com