# VLOOKUP & INDIRECT – Dynamic Range – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023

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.

``=VLOOKUP(\$B3,INDIRECT("'"&C\$2&"'!"&"B3:C5"),2,FALSE)``

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:

``'2018!'B3:C5``

So all we need to do is write a formula that will generate that range within the INDIRECT Function:

``"'"&C\$2&"'!"&"B3:C5"``

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.

``=CHOOSE(C2,VLOOKUP(B3,'2018'!B3:C5,2,FALSE),VLOOKUP(B3,'2019'!B3:C5,2,FALSE),VLOOKUP(B3,'2020'!B3:C5,2,FALSE))``

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.