A standard Excel VLOOKUP can refer to values from a specific worksheet. Financial and business planning models are often complex and detailed in their analytics, and therefore require high-level executive summaries or dashboards of multiple worksheets.
The following illustration facilitates the needs of a complex business planning and analysis spreadsheet, for multiple telephone destination markets across the world – each documented in a separate worksheet. It will perform a VLOOKUP on the specified Lookup_value from a variable worksheet input value.
This attached Excel spreadsheet demonstrates the power of a VLOOKUP with the INDIRECT function to reference a Lookup_value from multiple worksheets.
Strategize Financial Modelling – Sales Commission Dashboard
Multiple worksheet VLOOKUP – the INDIRECT function
A VLOOKUP incorporating the INDIRECT function can deliver a multiple worksheet analytical tool. The INDIRECT tool will permit the VLOOKUP Table_array to update based on a variable input worksheet name.
The one caveat is to maintain relatively uniform source worksheets, otherwise the VLOOKUP may be unable to reference the correct lookup value.
Business or financial analytical benefits of this approach
Whilst a PivotTable needs to be manually refreshed to reflect updated source data; this VLOOKUP will effortlessly flex to reflect modified source numbers.
Important points to remember with a VLOOKUP across multiple worksheets
Each worksheet, such as these country worksheet tables, must be 100% accurate in their column layout for the purposes of executing a correct VLOOKUP
Like a standard VLOOKUP, this multiple worksheet VLOOKUP must exactly reference the corresponding worksheet; otherwise a value of “ARG “ will be unable to reference a worksheet named “ARG” for example.