# INDEX and MATCH with multiple drop-downs

Occasional Contributor

# INDEX and MATCH with multiple drop-downs

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

Use FILTER().

# Re: INDEX and MATCH with multiple drop-downs

Dependent Drop-down Lists in Excel - Easy Excel Tutorial

# Re: INDEX and MATCH with multiple drop-downs

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))``

# Re: INDEX and MATCH with multiple drop-downs

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.

# Re: INDEX and MATCH with multiple drop-downs

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

# Re: INDEX and MATCH with multiple drop-downs

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