Dynamic Vlookup with INDIRECT() across multiple sheets

[UAL225]

Hello all,

Trying to get the INDIRECT function to work as intended. I think it should be doable. What I am trying to do is setup the value for "Qty" in "Dashboard" for Cat 1 - 5 based on the drop down selection in C1 And F1.

Where Person A Period 1 the cat's are as follows:
124
49
44
149
37

So if that is selected I am expecting to see these values populate in the dashboard and so on, based on the person / period of your selection. Any guidance is appreciated.

[CK76]

In B4:
=IFERROR(VLOOKUP($C$1,INDIRECT("'"&$E$1&" "&$F$1&"'!A2:F5"),MATCH(A4,INDIRECT("'"&$E$1&" "&$F$1&"'!A1:F1"),0),FALSE),"")

Copy down.

[BryanTX]

CK's answer solves your problem, but I thought I'd help with why what you had in your sheet wasn't working.

Your formula in B4 is this:

=IFERROR(VLOOKUP(A4,INDIRECT("'"&G$1&"'!B4:B8"),0,FALSE),"")

There are a few problems here.

VLOOKUP matches your value (in this case A4) by looking vertically down the first colum in your array. In this case B4:B8 doesn't work because all those values are in a row. To use VLOOKUP you need your lookup values to be in a column. That's not quite enough here because you also need to grab the right column. That's why CK combined Vlookup and Match, Match will pass the correct column number.

Your INDIRECT statement is pointing to $G$1, but your data actually lives in $F$1. In addition, your tabs are named "Period X", so you need to pass the word "Period" to your indirect statement as well, either by grabbing both E1 and F1 or by just putting the word "Period" in your concatenation.

Hope that helps

[UAL225]

Thanks for the detailed response!

So I get the F1 versus G1. However why also include the A2:F5? Is that pointing to the max size array of the tables in the tabs? So in turn the section for indirect at the match for A1:F1 is checking the the 'Cat 1", 'Cat 2', etc in the header to get the value?