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.

Sep 27, 2022 in Others 718 views

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.

• 63,720 points

How to use goal seek function in Excel user function?

In the Microsoft Excel Object-Sheet1(Sheet1), we can ...READ MORE

How to use VLOOKUP function in MS Excel

Here is the formula for cell G2: ...READ MORE

How to use Excel VLOOKUP function with words that begin with the letters AB

If you lookup "AB" in Excel, it ...READ MORE

How to use the flat button in Flutter?

FlatButton is really easy to use. Where ever ...READ MORE

Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)

Similar to Sean's fantastic response, mine also ...READ MORE

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE