# Using choose/match in place of Indirect



## elk03 (Dec 7, 2022)

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)


----------



## Domenic (Dec 8, 2022)

Try...

=VLOOKUP(B3,CHOOSE(MATCH(H2,{"Sheet1","Sheet2"},0),'Sheet'1!A:R,'Sheet2'!A:S),19,0)

Change and/or add sheet names, along with their corresponding references, accordingly.

Hope this helps!


----------



## elk03 (Dec 8, 2022)

Really appreciate it. That works great! Thanks.


----------



## Domenic (Dec 8, 2022)

You're very welcome, glad I could help.

Cheers!


----------



## elk03 (Monday at 3:00 PM)

Hi, I was wondering if in the Match function, for "Sheet1" and "Sheet2" to use a cell refence? I get an error message when trying to.

=VLOOKUP(B3,CHOOSE(MATCH(H2,{"Sheet1","Sheet2"},0),'Sheet'1!A:R,'Sheet2'!A:S),19,0)


----------



## Domenic (Monday at 3:58 PM)

Sure, let's say that J2 and J3 contain the sheet names Sheet1 and Sheet2, respectively...

=VLOOKUP(B3,CHOOSE(MATCH(H2,$J$2:$J$3,0),'Sheet1'!A:R,'Sheet2'!A:S),19,0)

By the way, you had a single quote in the wrong place.  Also, if you want the references to your lookup tables to be absolute...

=VLOOKUP(B3,CHOOSE(MATCH(H2,$J$2:$J$3,0),'Sheet1'!$A:$R,'Sheet2'!$A:$S),19,0)

Hope this helps!


----------



## elk03 (Monday at 5:01 PM)

Thanks for the reply. It does not seem to pull the correct sheet reference without the array part { } it seems like? All the months are pulling in differently than the one referred.


----------



## Domenic (Monday at 5:58 PM)

Can you post your revised formula?


----------



## elk03 (Tuesday at 9:01 AM)

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

Its pulling data for the wrong month based on D1.


----------



## Domenic (Tuesday at 10:08 AM)

What value does D1 contain?

And what values does G14:G19 contain?


----------



## elk03 (Dec 7, 2022)

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)


----------



## elk03 (Tuesday at 10:10 AM)

D1 is the month I'm looking for - May MT

This is g14-g19:


May MTJun MTJul MTAug MTSep MTOct MT


----------



## Domenic (Tuesday at 10:30 AM)

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!


----------



## elk03 (Tuesday at 11:14 AM)

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?


----------



## Domenic (Tuesday at 11:22 AM)

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.


----------



## elk03 (Tuesday at 11:25 AM)

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.


----------

