Using INDEX and MATCH that reference multiple drop-down lists

FShaikh

New Member
Joined
May 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:


Excel Formula:
=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 Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,841
Office Version
  1. 365
Platform
  1. Windows
With Excel 365 you could use the FILTER function.
In the example below F2 and G2 would be your drop down lists.

Book1
ABCDEFG
1NameMonthAmountFrom DropDown
2Name11April100Name11May
3Name12April101103
4Name12May102
5Name11May103
6Name15May104
7Name11June105
8Name11July106
Sheet1
Cell Formulas
RangeFormula
F3F3=FILTER(C2:C8,($A$2:$A$8=$F$2)*($B$2:$B$8=$G$2),"No Data")
 

FShaikh

New Member
Joined
May 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
With Excel 365 you could use the FILTER function.
In the example below F2 and G2 would be your drop down lists.

Book1
ABCDEFG
1NameMonthAmountFrom DropDown
2Name11April100Name11May
3Name12April101103
4Name12May102
5Name11May103
6Name15May104
7Name11June105
8Name11July106
Sheet1
Cell Formulas
RangeFormula
F3F3=FILTER(C2:C8,($A$2:$A$8=$F$2)*($B$2:$B$8=$G$2),"No Data")
Many thanks for assistance. I have managed to get the first half working, however the second part is a challenge. Let me explain...

So for the first part:

Excel Formula:
=FILTER(April21!$E$2:$E$36,(April21!$A$2:$A$36=$F$3)

This works. However the second part (which references the second drop-down list) doesnt work:

Excel Formula:
=FILTER(April21!E$2:E$36,(April21!$A$2:$A$36=$F$3)*(May21!E$2:E$36,(May21!$A$2:$A$36=$W$3)))

Any thoughts please?
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,841
Office Version
  1. 365
Platform
  1. Windows
The syntax in the last part of your formula (in red below) is wrong
FILTER(April21!E$2:E$36,(April21!$A$2:$A$36=$F$3)*(May21!E$2:E$36,(May21!$A$2:$A$36=$W$3)))
What is this part of the formula trying to do?
(May21!E$2:E$36,(May21!$A$2:$A$36=$W$3))
Are you trying to get two different sets of data from sheets April21 and May21? If so, what do you want to do with this data when it is retrieved?
Can you post a small sample of your data and the expected results?
 

FShaikh

New Member
Joined
May 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
The syntax in the last part of your formula (in red below) is wrong
FILTER(April21!E$2:E$36,(April21!$A$2:$A$36=$F$3)*(May21!E$2:E$36,(May21!$A$2:$A$36=$W$3)))
What is this part of the formula trying to do?
(May21!E$2:E$36,(May21!$A$2:$A$36=$W$3))
Are you trying to get two different sets of data from sheets April21 and May21? If so, what do you want to do with this data when it is retrieved?
Can you post a small sample of your data and the expected results?
As per the mini-sheet below, I have sheet 1 with 2 drop-down lists (cells B1 and B2). When I select a name, columns 1-4 below are populated by the data in the Apr sheet (see attached screenshot for workbook structure).

What I want to do is: When I select a name AND a month from the 2 drop-down lists, the data for the selected name is displayed from the corresponding months worksheet.

FYI - all tables listed in the 'month' worksheets have the same structure and position.

I hope this makes more sense.

Book1.xlsx
ABCD
1Namename4
2Date
3
4
5
6Col1Col2Col3Col4
755789
8
Sheet1
Cell Formulas
RangeFormula
A7:D7A7=FILTER(Apr!B$2:B$5,(Apr!$A$2:$A$5=$B$1))
Cells with Data Validation
CellAllowCriteria
B1List=data!$A$1:$A$4
B2List=data!$C$1:$C$4
 

Attachments

  • Screenshot 2021-05-21 123702.png
    Screenshot 2021-05-21 123702.png
    3.1 KB · Views: 3

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,841
Office Version
  1. 365
Platform
  1. Windows
See if this works for you.
Enter the formula in A7 and the answers should spill into the other columns.
The INDIRECT function should find the right sheet for each month.
Change cell references to match your data.

Book3
ABCD
1Namename4
2DateApr21
3
4
5
6Col1Col2Col3Col4
755789
Sheet1
Cell Formulas
RangeFormula
A7:D7A7=FILTER(INDIRECT($B$2&"!$B$2:$E$5"),INDIRECT($B$2&"!$A$2:$A$5")=$B$1)
Dynamic array formulas.


Apr21 sheet

Book3
ABCDE
1Name
2Name150323418
3Name225214934
4Name336181829
5Name455789
Apr21
 

Forum statistics

Threads
1,141,816
Messages
5,708,751
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top