Forum Discussion

PMGlobal's avatar
PMGlobal
Copper Contributor
May 18, 2021

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.

excel

6 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 21, 2021

    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's avatar
    Yea_So
    Bronze Contributor
    May 21, 2021

    Hi PMGlobal 

    You're talking about dependent dropdowns:

    https://www.excel-easy.com/examples/dependent-drop-down-lists.html#:~:text=%5BSolution%5D%20Excel%20Dependent%20Drop%20down%20List%201%20On,the%20Source%20box%20and%20type%20%3DFood.%20See%20More.

    • PMGlobal's avatar
      PMGlobal
      Copper Contributor
      May 21, 2021

      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.

Resources