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 ...
excel
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.

    • PMGlobal's avatar
      PMGlobal
      Copper Contributor
      May 21, 2021
      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

Resources