# index match formula with sheet reference

#### valmir

##### Board Regular
Hello everyone! I am using this formula to look for a specific value in a range located on a sheet called "CAA"
=INDEX(CAA!BT5:CAA!BT34;MATCH(13;CAA!BS5:CAA!BS34;0))
My question is: since I have a cell named exactly as the sheet, can I use that cell reference to redirect it to the sheet, so that whenever I want to use that sheet I'll just use that cell reference?
Basically what I would like to do is to replace "CAA!" with this particular cell reference.
If this is possible, how would that replacement look like in this formula?

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### jasonb75

##### Well-known Member
You would need to use INDIRECT for that. With the sheet name in A1, it would be something like this.
Excel Formula:
``=INDEX(INDIRECT("'"&\$A\$1&"'!BT5:BT34");MATCH(13;INDIRECT("'"&\$A\$1&"'!BS5:BS34");0))``

Note that use of INDIRECT is a bad idea and should only be considered when absolutely necessary. The link below explains the reasons why.

#### valmir

##### Board Regular
You would need to use INDIRECT for that. With the sheet name in A1, it would be something like this.
Excel Formula:
``=INDEX(INDIRECT("'"&\$A\$1&"'!BT5:BT34");MATCH(13;INDIRECT("'"&\$A\$1&"'!BS5:BS34");0))``

Note that use of INDIRECT is a bad idea and should only be considered when absolutely necessary. The link below explains the reasons why.
Yessssss!!!! Thank you, thank you, thank you!!!!

Replies
3
Views
55
Replies
15
Views
495
Replies
3
Views
81
Replies
13
Views
72
Replies
4
Views
92

1,130,214
Messages
5,640,912
Members
417,179
Latest member
DavidFamilytree

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back