INDIRECT Function in Excel

If you want a formula that builds its own cell reference out of text, like a sheet name typed in a cell or an address you piece together, the INDIRECT function is what you need.

It takes a text string and turns it into a real, working reference to a cell or a range.

One thing to know up front. INDIRECT is volatile, so it recalculates whenever Excel recalculates, even if its own inputs have not changed.

INDIRECT can also return a range that spills in Microsoft 365, Excel 2021, Excel 2024, and Excel for the web. The single-cell references in the examples below return one value.

In this article, I’ll show you how to use INDIRECT with a few practical examples.

INDIRECT Syntax

Here is the syntax of the INDIRECT function:

=INDIRECT(ref_text, [a1])

• ref_text – the text string that describes the reference you want. This can be a cell address like “B2”, a range like “C2:C7”, a sheet reference like “Jan!B2”, or a named range. If the text is not a valid reference, INDIRECT returns a #REF! error.
• a1 – an optional logical value that tells INDIRECT which reference style the text uses. Leave it out or set it to TRUE for the normal A1 style. Set it to FALSE to read the text as an R1C1 style reference.

When to Use the INDIRECT Function in Excel

Use INDIRECT when you need to:

• Turn a text address into a live reference, so the address can come from another cell or be built with a formula.
• Pull data from a sheet whose name sits in a cell, which is handy for summary dashboards.
• Keep a reference fixed in place even when rows or columns are inserted around it.
• Drive dependent drop-down lists off named ranges.

Let me show you how this works with a few practical examples.

Example 1: Reference a Cell From a Text Address

Let’s start with the simplest case.

Below is a small summary table. Column A has the metric name and column B has its value.

I want to pull the value from cell B2 by describing that cell as plain text.

Here is the formula:

=INDIRECT("B2")

Here, INDIRECT takes the text string “B2”, reads it as an address, and returns what is actually in B2, which is 8500.

On its own this looks pointless, since you could just type =B2. The value comes when that text is not hard-coded but sits in a cell or gets built by a formula, which is what the next examples do.

Example 2: Build a Reference From Parts to Fetch Any Row

Here’s where INDIRECT starts to earn its keep.

Below is a price list. Column A has the product and column B has its price, running from row 2 to row 6.

I want to type a row number in cell D2 and have the formula fetch the price from that row automatically.

Here is the formula:

=INDIRECT("B"&D2)

In the above formula, the text “B” is joined to the number in D2 using the ampersand. With 4 in D2, that builds the string “B4”, and INDIRECT turns it into a reference to cell B4, returning 190.

Change D2 to another row number and the result updates on its own. This example demonstrates how to build an A1 reference as text.

For an ordinary lookup by row position, INDEX is simpler and nonvolatile. The reader file includes an INDEX comparison beside this text-reference example.

Example 3: Pull Data From a Sheet Named in a Cell

This next one is something I use often for summary dashboards that reference data from another sheet.

Say you have a separate sheet for each month, named Jan, Feb, and Mar, and each of those sheets holds that month’s total revenue in cell B2. On a dashboard sheet, cell A2 holds the month name you want to look at.

I want the dashboard to pull the revenue from whichever sheet is named in A2, without editing the formula each time.

Here is the formula:

=INDIRECT("'"&A2&"'!B2")

How this formula works:

• “‘”&A2&”‘” wraps the sheet name from A2 in single quotes, so a name with spaces (like “Q1 Data”) still works.
• &”!B2″ adds the cell you want from that sheet.
• With Feb in A2, the string becomes ‘Feb’!B2, and INDIRECT returns 42000, the value sitting in Feb!B2.

Switch A2 to Jan or Mar and the dashboard reads from that sheet instead. One formula covers every month.

Pro Tip: Single quotes handle sheet names with spaces, such as “Sales Data”. If the sheet name itself contains an apostrophe, double that apostrophe inside the reference text. The simple formula above assumes the name has no apostrophe.

Example 4: Lock a Range So Inserting Rows Doesn’t Shift It

Let’s use INDIRECT to keep a range fixed when rows are inserted.

Below is a list of monthly expenses in the range B2:B7.

I want a total that always sums B2:B7, even if someone inserts a new row above the range.

Here is the formula:

=SUM(INDIRECT("B2:B7"))

This returns 6850, the total of the six values.

A normal =SUM(B2:B7) adjusts itself when you insert or delete rows around it, shifting to follow the data to a new address like B3:B8.

Because INDIRECT reads “B2:B7” as plain text, Excel never rewrites it. The total stays pinned to those exact cell addresses no matter what you insert around them.

This suits a sheet where you insert new entries at the top and always want the first six rows, B2:B7. If your total should follow the data when rows move, use a plain =SUM(B2:B7) instead.

Example 5: Average a Named Range Chosen by Text

Here’s a clean one that shows off how INDIRECT reads named ranges.

Below is a table of marks for five students across three subjects. Column B holds the Math scores, column C the Physics scores, and column D the Chemistry scores.

I’ve named B2:B6 as Math, C2:C6 as Physics, and D2:D6 as Chemistry. I want the average Math score by passing that range name in as plain text.

Here is the formula:

=AVERAGE(INDIRECT("Math"))

INDIRECT reads the text “Math” as the name of a range, so it hands back the reference B2:B6. AVERAGE then works on those five scores and returns 80.

The real payoff is driving the subject from a cell. Put the subject name in F2 and point INDIRECT at that cell instead.

=AVERAGE(INDIRECT(F2))

With Math in F2, this returns 80. Type Physics or Chemistry into F2 and the same formula switches to that subject’s range and recalculates, no editing needed.

Pro Tip: The range name here is not in quotes because F2 already holds it as text. Only wrap a name in quotes when you type it straight into the formula, like “Math” in the first version.

Example 6: Create Dependent Drop-Down Lists With INDIRECT

This is probably the most popular use of INDIRECT, and it makes data entry a lot cleaner.

Say you have three category lists set up as columns: Fruits (Apple, Banana, Mango), Vegetables (Carrot, Potato, Onion), and Dairy (Milk, Cheese, Yogurt). You want a category drop-down in E2 and a second drop-down in F2 that only shows items from the chosen category.

First, create one named range for each category, with the range name matching the header exactly, so a range named Fruits holds Apple, Banana, and Mango. Then set up the first drop-down in E2 with the three category names.

Now build the dependent drop-down in F2:

• Select cell F2.
• Go to the Data tab and click Data Validation.
• Under Allow, choose List.
• In the Source box, enter the formula below, then click OK.

=INDIRECT($E$2)

Now when E2 shows Fruits, INDIRECT turns that text into a reference to the named range Fruits, so the F2 drop-down lists only Apple, Banana, and Mango.

Pick Dairy in E2 instead and the F2 drop-down switches to Milk, Cheese, and Yogurt.

Pro Tip: Named ranges can’t contain spaces, so a category like “Frozen Foods” would break this. Name the range Frozen_Foods and use =INDIRECT(SUBSTITUTE($E$2,” “,”_”)) as the source, which swaps the space for an underscore before INDIRECT reads it.

Example 7: Reference R1C1 Style With the a1 Argument

For this example, let’s use the optional a1 argument.

Below is a small grid of values in A1:C3. Cell E2 holds a row number and F2 holds a column number.

I want to fetch a value using those row and column numbers directly, which the R1C1 style is built for.

Here is the formula:

=INDIRECT("R"&E2&"C"&F2,FALSE)

Here, the a1 argument is set to FALSE, which tells INDIRECT to read the text as an R1C1 reference (R for row, C for column).

With 3 in E2 and 2 in F2, the string becomes “R3C2”, meaning row 3, column 2, so INDIRECT returns 80, the value in cell B3.

This shows how R1C1 text uses a number for both the row and the column. FALSE tells INDIRECT to read that reference style.

For an ordinary lookup by row and column position, INDEX avoids building a text address and is nonvolatile. The reader file includes that simpler comparison beside this R1C1 demo.

Example 8: Switch the VLOOKUP Table With INDIRECT

You can also use INDIRECT to choose which table VLOOKUP searches.

Below are two price tables for the same five products. A1:B6 holds the 2025 prices, and D1:E6 holds the 2026 prices. G2 contains Monitor, H2 contains 2026, and I2 is empty.

Each table’s data has a named range. Prices_2025 refers to A2:B6, and Prices_2026 refers to D2:E6.

I want the price of Monitor in I2, using the year in H2 to choose the table.

Here is the formula:

=VLOOKUP(G2,INDIRECT("Prices_"&H2),2,FALSE)

This returns 205, the 2026 price of Monitor.

The expression “Prices_”&H2 builds the text “Prices_2026”. INDIRECT turns that text into the named range D2:E6.

VLOOKUP searches that range for Monitor and returns its price from the second column. FALSE tells it to use an exact match.

Change H2 to 2025, and the same formula returns 190, the 2025 Monitor price.

To add another year, create its table, name the data range Prices_2027, and enter 2027 in H2. You don’t need to edit this formula. An IF or SWITCH formula would need an edit for each new year.

If you enter 2027 before creating Prices_2027, the formula returns #REF! because there’s no matching named range.

Tips & Common Mistakes

• INDIRECT is volatile. It recalculates every time anything on the sheet changes, not just when its own inputs change. A handful of INDIRECT formulas is fine, but hundreds of them can noticeably slow a large workbook, so use it only where you actually need a text-driven reference.
• Renaming can break a text reference. A normal reference updates when you rename a sheet or a named range. INDIRECT cannot update hard-coded text. Rename Feb to February without updating the text, and =INDIRECT(“Feb!B2”) returns #REF!.
• External workbook references have limits. On desktop Excel, INDIRECT can only pull from an external workbook that is currently open. A closed source returns #REF!. External references through INDIRECT are not supported in Excel for the web.
• Watch for #REF! errors. If the text doesn’t resolve to a valid reference (a typo in the address, a missing named range, or missing quotes around a sheet name with spaces), INDIRECT returns #REF!. Check the text string first when troubleshooting.

INDIRECT is one of those functions that looks odd until the moment you need it.

Once you do, it is the cleanest way to turn text into a live reference, whether that text is a sheet name on a dashboard, an address you build with a formula, or the source behind a dependent drop-down list.

Try it on your own data with the examples above and it will quickly make sense.

List of All Excel Functions

Other Excel Articles You May Also Like:

• How to Create a Dependent Drop Down List in Excel
• 3D References in Excel
• ADDRESS Function in Excel
• SHEET Function in Excel