INDEX and MATCH with multiple drop-downs

%3CLINGO-SUB%20id%3D%22lingo-sub-2366068%22%20slang%3D%22en-US%22%3EINDEX%20and%20MATCH%20with%20multiple%20drop-downs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2366068%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20drafted%20a%20monthly%20report%20which%20has%20a%20main%20dashboard%20sheet%2C%20along%20with%20monthly%20report%20sheet%20(the%20plan%20is%20to%20add%20additional%20sheets%20as%20the%20months%20progress).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithin%20the%20dashboard%20sheet%20I%20have%20a%20summary%2C%20so%20every%20time%20I%20select%20a%20name%20from%20a%20drop-down%20it%20displays%20key%20data%20pulled%20from%20the%20monthly%20sheet.%20The%20function%20to%20achieve%20this%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(April21!E%242%3AE%2436%2CMATCH(F3%2CApril21!A%242%3AA%2436%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20F3%20is%20the%20drop-down%20list%20in%20my%20dashboard%20sheet%2C%20and%20it%20is%20pulling%20data%20from%20the%20April21%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%20this%3A%20Can%20I%20reference%20multiple%20drop-down%20lists%20within%20this%20function%3F%20If%20I%20select%20a%20name%20from%20the%20first%20drop-down%20list%2C%20then%20the%20month%20from%20a%20second%20drop-down%20list%2C%20it%20will%20show%20the%20data%20for%20that%20name%20from%20that%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20most%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2366068%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2366434%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20and%20MATCH%20with%20multiple%20drop-downs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2366434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379070%22%20target%3D%22_blank%22%3E%40PMGlobal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20FILTER().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2374429%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20and%20MATCH%20with%20multiple%20drop-downs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2374429%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379070%22%20target%3D%22_blank%22%3E%40PMGlobal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20talking%20about%20dependent%20dropdowns%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excel-easy.com%2Fexamples%2Fdependent-drop-down-lists.html%23%3A~%3Atext%3D%255BSolution%255D%2520Excel%2520Dependent%2520Drop%2520down%2520List%25201%2520On%2Cthe%2520Source%2520box%2520and%2520type%2520%253DFood.%2520See%2520More.%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EDependent%20Drop-down%20Lists%20in%20Excel%20-%20Easy%20Excel%20Tutorial%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2374963%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20and%20MATCH%20with%20multiple%20drop-downs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2374963%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379070%22%20target%3D%22_blank%22%3E%40PMGlobal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%2C%20if%20G3%20returns%20sheet%20name%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(INDIRECT(%22'%22%26amp%3B%24G%243%20%26amp%3B%20%22'!E%242%3AE%2436%22)%2CMATCH(F3%2CINDIRECT(%22'%22%26amp%3B%24G%243%20%26amp%3B%20%22'!A%242%3AA%2436%22)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375530%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20and%20MATCH%20with%20multiple%20drop-downs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375530%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20really.%20The%20lists%20dont%20need%20to%20be%20dependent.%20List%201%20%3D%20Name%2C%20List%202%20%3D%20Month.%20So%20List%202%20will%20remain%20the%20same%20whichever%20name%20is%20chosen.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERather%2C%20I%20want%20to%20use%20the%20INDEX%20and%20MATCH%20functions%20together%20with%202%20separate%20drop-down%20lists%20to%20pull%20the%20same%20data%20from%20a%20range%20of%20worksheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20use%20INDEX%2FMATCH%20with%201%20drop-down%20no%20worries.%20So%20when%20I%20select%20a%20name%20from%20the%20list%20it%20displays%20the%20data%20from%20Month1%20worksheet.%20Getting%20it%20to%20work%20with%202%20lists%20is%20my%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20main%20worksheet%20(with%20the%202%20lists%2C%20and%20summary%20data)%2C%20then%20several%20other%20worksheets%20named%20by%20month%20(these%20all%20have%20the%20same%20table%20layout%2C%20but%20different%20data%20sets)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20when%20I%20select%20a%20name%20AND%20a%20month%20from%20the%202%20lists%2C%20I%20want%20it%20to%20show%20the%20data%20for%20that%20month%20(for%20the%20named%20person).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20copy%20of%20the%20file%20below%20if%20it%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375959%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20and%20MATCH%20with%20multiple%20drop-downs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375959%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(INDIRECT(%22'%22%26amp%3B%20%24B%242%20%26amp%3B%20%22'!B%242%3AB%245%22)%2CINDIRECT(%22'%22%26amp%3B%20%24B%242%20%26amp%3B%20%22'!A%242%3AA%245%22)%3D%24B%241)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379070%22%20target%3D%22_blank%22%3E%40PMGlobal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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