index match formula with sheet reference

valmir

Board Regular
Joined
Feb 10, 2021
Messages
235
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
Solution
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:??
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top