index match formula with sheet reference

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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!!!! :biggrin: :biggrin::biggrin:👏👏:biggrin:👏👏
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top