excelexcel-indirect

How to use the INDIRECT function in Excel

I am using in Excel the following formula that works to get the value of a cell:

=INDEX(Sheet1!$1:$1048576,MATCH("Component 1",Sheet1!$A:$A,0)+MATCH("Component 2",Sheet2!$B:$B,0),2)

For a new application, the sheet and component names are dynamic, meaning that they are allowed to change and are not necessarily "Sheet1", "Sheet2", "Component 1" and "Component 2". Therefore I have tried to twist the above formula using the build-in INDIRECT function. I have tried two different ways to make it work:

1-

=INDEX(INDIRECT(A1&"!$1:$1048576"),INDIRECT("MATCH("&CHAR(34)&B1&CHAR(34)&","&A1&"!$A:$A,0)+MATCH("&CHAR(34)&B2&CHAR(34)&","&A2&"!$B:$B,0)"),2)

2-

=INDIRECT("INDEX("&A1&"!$1:$1048576,MATCH("&CHAR(34)&B1&CHAR(34)&","&A1&"!$A:$A,0)+MATCH("&CHAR(34)&B2&CHAR(34)&","&A2&"!$B:$B,0),2)")

where cells A1 and A2 values are the sheets' names and cells B1 and B2 receive the components' names.

Both approaches gives an error #Ref!. But I really cannot see what is wrong.

I would really appreciate any information for me to understand my mistakes and any suggestions to improve these formulas.

Thanks in advance!

Solution

  • Reason

    You are using INDIRECT to compile parts of your formula, including the function names (e.g. "MATCH" in your "1-").

    Instead, only use INDIRECT to build the actual references to ranges or cells (including qualifiers such as worksheet, workbook etc) inside those functions within your formula. Leave the rest of the formula outside the INDIRECT function.

    So in your "1-", move "MATCH" back outside the "INDIRECT" function.

    To get the "Component 1" to be volatile, just reference a cell containing that string in the normal way.

    In your attempt "2-", you tried putting more of the formula inside the INDIRECT function, but it should have been less.

    Answer

    For example, you could try:

    =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDIRECT($A$1&"!$A:$A"),0)+MATCH($B$2,INDIRECT($A$2&"!$B:$B"),0),2)

    Even better

    The difficulty is arising in part due to putting the sheet name in a range and the range reference hard coded in the formula.

    1. You could consider putting "Sheet1!$1:$1048576" into A1 (for example), instead of just "Sheet1", and adjust formula accordingly. That way you can just edit A1, B1, B2 if the ranges change or grow, rather than editing the formula. Note that you would not be able to reuse A1 for the reference to "Sheet1!$A:$A", you would need a different cell to store that reference.

    2. Also consider naming the cells A1, A2, B1 & B2, and referring to the named ranges in your lookup formula. That way if you move those cells around in certain ways it will be less prone to breaking your lookup formula.

    Further thoughts

    I presume you are across the performance tradeoffs of using volatile functions such as INDIRECT. If not, happy searching!