How to use the INDIRECT function in Excel

Question

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 give 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.

Answer 1

Try this:

=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)

I hope this helps you.