Using choose/match in place of Indirect

elk03

Board Regular
Joined
Jan 30, 2020
Messages
98
Office Version
  1. 2019
Hi,

I currently have a formula using INDIRECT but was hoping to use Choose and Match in place to keep spreadsheet fast. This is my current formula below, where H2 is the current name of the worksheet I want to reference. I want to variably change the worksheet reference based on H2. Thanks for the help!


=VLOOKUP(B3,INDIRECT("'"&H2&"'!"&"A:S"),19,0)
 
D1 is the month I'm looking for - May MT

This is g14-g19:

May MT
Jun MT
Jul MT
Aug MT
Sep MT
Oct MT
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
MATCH searches for the item in your range of cells and returns its relative position in that range. In your example, MATCH returns 1, so CHOOSE returns the first value in its list. So you'll need to match the order of the values listed in CHOOSE with the order listed in your range of cells...

=VLOOKUP($B3,CHOOSE(MATCH(D$1,$G$14:$G$19,0),'May MT'!$A:$S,'Jun MT'!$A:$S,'Jul MT'!$A:$S,'Aug MT'!$A:$S,'Sep MT'!$A:$S,'Oct MT'!$A:$S),19,0)

Hope this helps!
 
Upvote 0
Ah ok. I guess the issue is then, those months on each tab will be changing as the months go by, so the order will be changing. I'm assuming it won't work without those sheet names in the right order?
 
Upvote 0
The order of tabs/sheets within your workbook doesn't matter. It only matters that the order of your sheet names listed in your range $G$14:$G$19 matches the order of values listed in CHOOSE.
 
Upvote 0
Got it, that is what I meant. Thanks a lot for your help. I should be able to work around this now. Really appreciate it.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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