VLOOKUP & INDIRECT – Excel & Google Sheets

This tutorial will demonstrate how to use the INDIRECT Function to define the lookup range in Excel and Google Sheets.

vlookup indirect Main Function

INDIRECT & VLOOKUP

You may need to perform a VLOOKUP on multiple ranges at once, dependent on certain cell values. For these instances, the INDIRECT Function can be used to define a lookup range, or even create a dynamic reference to multiple sheets.

vlookup indirect Summary

In the example above we have data in the B3:C5 range on every sheet that we want to perform an exact match lookup for and create a summary. Instead of manually changing the sheet names, we can dynamically refer to the sheets with the INDIRECT Function.

We need the lookup range for C3 to look like:

 

The INDIRECT Function lets us define the range without hard-coding “2018”. That way, the formula can be copied over for other years.

CHOOSE & VLOOKUP

The INDIRECT Function is “volatile.” It recalculates every time Excel does, and that can cause your workbook to calculate slowly. You can often accomplish the same task by using other functions. Below we will demonstrate how to use the CHOOSE Function instead of INDIRECT to define the lookup range. The CHOOSE Function takes an index number and a list of values to return a single value from the list.

vlookup indirect Choose

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.

vlookup-indirect Google Function

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!