Nested Indirect() Functions returning #REF

Question

I have two sheets of data, with matching data in mismatched rows.

On one sheet, I have a list of values.

On the other, I have a list of numbers which correspond to the row number on the first sheet that contains the matching value that I am trying to automatically copy to the second sheet.

Here's my code:

=INDIRECT("'Combined ICME+ipShocks List'!"&(INDIRECT("A"&H3)))

So it should be pointing to, on the sheet named "Combined ICME+ipShocks List", the cell in column A with the row determined by cell H3 on the current sheet.

Replacing what I have written for the second Indirect() with a simple cell (e.g. H3) works, so I suspect it has something to do with the second Indirect() that I have written.

Answer 1

To answer the question why this throws an error:

Indirect needs a text value that resolves to a cell address.

In your formula you are nesting two Indirect() functions.

The inner Indirect will resolve to a cell reference like A1 (as a reference, not as text). You then concatenate that cell reference with a string and feed that to the outer Indirect(). That is what causes the error. Indirect expects a string. You are feeding it a string concatenated with a cell reference, which is not a combination any function will understand.

You don't need the inner Indirect at all if you just include the column letter in the string constant.

=INDIRECT("'Combined ICME+ipShocks List'!A"&H3)

Take note that Indirect is volatile and will cause the workbook to be recalculated whenever any cell changes. That can make you workbook slow. Indirect should be used very sparingly. In this case, Index can be used instead, as suggested by Jeeped in a comment to your question.

=INDEX('Combined ICME+ipShocks List'!A:A,H3)

Since you know the sheet name and the column, only the row number is the variable. In this case, Indirect is overkill. Use Indirect only when you need to construct the sheet name with a formula.