Using choose/match in place of Indirect

[elk03]

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]

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]

Really appreciate it. That works great! Thanks.

 

[Domenic]

You're very welcome, glad I could help.

Cheers!

 

[elk03]

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]

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]

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]

Can you post your revised formula?

 

[elk03]

=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]

What value does D1 contain?

And what values does G14:G19 contain?