INDEX and MATCH with multiple drop-downs

Copper Contributor

I have drafted a monthly report which has a main dashboard sheet, along with monthly report sheet (the plan is to add additional sheets as the months progress).

 

Within the dashboard sheet I have a summary, so every time I select a name from a drop-down it displays key data pulled from the monthly sheet. The function to achieve this looks like this:

 

=INDEX(April21!E$2:E$36,MATCH(F3,April21!A$2:A$36,0))

 

So F3 is the drop-down list in my dashboard sheet, and it is pulling data from the April21 sheet.

 

My question is this: Can I reference multiple drop-down lists within this function? If I select a name from the first drop-down list, then the month from a second drop-down list, it will show the data for that name from that month.

 

Any assistance would be most appreciated.

6 Replies

@PMGlobal 

Use FILTER().

 

@PMGlobal 

As variant, if G3 returns sheet name

=INDEX(INDIRECT("'"&$G$3 & "'!E$2:E$36"),MATCH(F3,INDIRECT("'"&$G$3 & "'!A$2:A$36"),0))

@Yea_So 

 

Not really. The lists dont need to be dependent. List 1 = Name, List 2 = Month. So List 2 will remain the same whichever name is chosen.

 

Rather, I want to use the INDEX and MATCH functions together with 2 separate drop-down lists to pull the same data from a range of worksheets.

 

I can use INDEX/MATCH with 1 drop-down no worries. So when I select a name from the list it displays the data from Month1 worksheet. Getting it to work with 2 lists is my issue.

 

I have the main worksheet (with the 2 lists, and summary data), then several other worksheets named by month (these all have the same table layout, but different data sets)

 

So when I select a name AND a month from the 2 lists, I want it to show the data for that month (for the named person).

 

I have attached a copy of the file below if it helps.

 

=FILTER(INDIRECT("'"& $B$2 & "'!B$2:B$5"),INDIRECT("'"& $B$2 & "'!A$2:A$5")=$B$1)

@PMGlobal 

Thanks Sergei - I have got it to work....sort of? Selecting different name and date combinations seems to be a little buggy. Works for some, doesn't work for others.

I will have a play around over the weekend and come back to you.

Thanks again - most appreciated