VLOOKUP & INDIRECT – Dynamic Range – Excel & Google Sheets
Download the example workbook
This tutorial will demonstrate how to use the INDIRECT Function to define the lookup range in Excel and Google Sheets.
INDIRECT & VLOOKUP
You may need to perform a VLOOKUP on multiple ranges at once, dependent on certain cell values. If so, one option is the INDIRECT Function, which can be used to define a lookup range, or even create a dynamic reference to multiple sheets.
Here we have data in range B3:C5 on every sheet. Instead of writing a VLOOKUP formula for each sheet, we can dynamically refer to the sheets with the INDIRECT Function.
INDIRECT – Dynamic Lookup Range
We need the lookup range for C3 to look like:
So all we need to do is write a formula that will generate that range within the INDIRECT Function:
We use (&) to merge together the text along with the partially-locked cell reference containing the year (C$2). Then when the formula is copied across the range, the INDIRECT function will output the necessary sheet for each column.
CHOOSE & VLOOKUP
The INDIRECT Function is “volatile.” It recalculates every time Excel does, and that can cause your workbook to calculate slowly.
Instead, in this case, the CHOOSE Function might be a better alternative. The CHOOSE Function let’s you “choose” what to output from a list.
In this example, the list in the CHOOSE Function is each possible VLOOKUP formula. Each range is hard-coded, and each cell references all three sheets. The index value in Row 2 tells the function which element of the list to use, i.e. which sheet to perform the lookup on.
VLOOKUP & INDIRECT in Google Sheets
These formulas work the same in Google Sheets as in Excel.