Is there any scope of an Indirect in Excel vs Indirect in Google Sheets comparison? This question was in my mind since the last few months.

From my past experience, the use of the Indirect function in Excel and in Google Sheets seemed identical. But later I have realized that there are a few differences that are worth to point out.

Here is that eyeopening experience. I thought that the Indirect function in Excel supports array formula use. It was on the contrary in most of the cases. That prompts me to experiment with the Indirect function further in both the applications.

This tutorial, i.e. the comparison of Indirect in Google Sheets and Excel, is the output of that experiment.

I am an expert in Google Sheets, not in Excel. That, of course, limits my experiment to some extent. If you find anything significant to include in this comparison of Indirect in Excel vs Indirect in Google Sheets, please let me know.

Indirect in Excel vs Indirect in Google Sheets

First, let us see how to use the Indirect function in Excel and also in Google Sheets.
I have already a tutorial on how to use Indirect in Google Sheets.

In this section, I am just checking whether the Indirect function works similarly in Excel. On that process, I have included how to use Indirect in Excel and Google Sheets.

Indirect Function Similarities

Below you can find Index formula examples that work both in Excel and Google Sheets. The following two formulas demonstrate how you can similarly use the Indirect function in Excel as well as in Google Sheets.

The Basic Use of Indirect Function in Sheets and Excel

Suppose you have # 500 in cell A4. You can get that cell value in another cell in two ways.

Formula 1:

=A4

Formula 2:

=indirect("A4")

The Indirect function returns the reference specified as a text string in Excel and Google Sheets. That means you can type the cell reference A4 in any cell and use it in Indirect as the text string.

Excel Screenshot:

The Indirect function opens a whole lot of opportunities in using cell references in Excel/Google Sheets formulas.

Go through the below Indirect formulas (please see the screenshot) to understand how to use numbers and alphabets in cells to form cell references using the Indirect.

Google Sheets Screenshot:

Both the Excel and Google Spreadsheets supports named ranges in its Indirect function. So again there is no reason to complain.

Indirect Named Ranges in Sheets and Excel

In the Indirect in Excel vs Indirect in Google Sheets comparison, one cannot skip the role of named ranges.

How you name a range (named ranges) is different in Excel and Google Sheets. But that doesn’t affect the Indirect function use in most of the cases.

Normally you can name a range by selecting it and then right-click to open the context menu. In Excel, you can see the option “Name a Range…” but in Google Sheets, it would be ‘Define named range”

This Indirect formula with named range works in both Google Sheets and Excel.

Indirect Cell Reference in Another Tab in Sheets and Excel

The below example well explains how to properly use the Indirect function to refer to a different sheet tab.

In cell A1 in “Sheet2”, I have entered the text string Prashanth KV.

The above experiment is in Sheet1 which shows how to refer to a different Sheet in Indirect.

Indirect Function Differences

When you compare the function Indirect in Google Sheets and Excel, you cannot find many differences.

I found the following differences in my Indirect in Excel vs Indirect in Google Sheets comparison.

Indirect Named Ranges – Difference in Sheets and Excel

Excel requires the formula to enter as an array formula when you want to populate a range using Indirect and Named Ranges.

In Google Sheets, the Indirect and Named Range combination can populate a range without using the ArrayFormula function.

Example:

Name the range A1:A12 as “month” the way you do it in your spreadsheet solution. In cell C1, enter the string “month” without double quotes.

Then use the Indirect formula as follows.

=indirect(C1)

It would populate the data defined by the named range in Google Sheets. But in Excel, it won’t.

In Excel, the formula must be entered as an Array Formula using Ctrl+Shift+Enter.

{=INDIRECT(C1)}

Indirect Array Formula – Difference in Sheets and Excel

The below Indirect formula would only work in Excel. In Google Sheets, it won’t.

The example in Excel that Works:

What does this formula do?

The above formula counts the occurrences of the string Apple in column A in “Sheet2”, “Sheet3”, and “Sheet4”.

In Google Sheets, this formula won’t work. You can only include Indirect reference to one sheet at a time.

There enter the below formula in cell D2 and drag down.

=SUMPRODUCT(COUNTIF(INDIRECT(A2&"!$A:$A"),$C$2))

Excel Indirect function really scores here! I really want this to work in Google Sheets to avoid workaround like this – How to Include Future Sheets in Formulas in Sheets.

Indirect in Drop-down List in Excel and Sheets

As mentioned above, the Indirect + Named Range in Sheets can populate an array result. Here the Excel function fails. Actually, this is a blessing in disguise for Excel users. Do you know why?

You can use Excel Indirect function to create a dependent drop-down list as below.

I have two named ranges in Excel as below.

In cell A2, I have created a drop-down list with two items – “fruits” and “vegetables”

In cell B2 in Excel, I can easily create a dependent drop-down (drop-down that depends on another drop-down) using the Indirect function with named ranges.

Since the Excel Indirect doesn’t expand as an array, it outputs as a list in cell B2. We can’t use the Indirect function as above in Google Sheets. Here you can to use a script or a workaround like this – Multi-Row Dynamic Dependent Drop Down List in Google Sheets.

Additional Resources:

1. Role of Indirect Function in Conditional Formatting in Google Sheets.