# List names using multiple Criteria from dependent drop down

#### politicalangel

##### New Member
Does anyone have a formula I can use that will be able to pull values based on multiple drop downs?
My data is over 2000000 lines- I am trying to create a summary sheet where we use the drop down to summarize by region travel expenses on a monthly basis.
Im trying not to use a Pivot

I am looking for J9 downwards to populate with the names of the individuals based on the Selected Region, Selected Category and selected Job title (if you can make the drop down dependent even better)

#### Attachments

• 11.1 KB Views: 25

#### Chihiro

##### Excel Ninja
Why the aversion to Pivot Table?
It's probably the most efficient method to accomplish what you are after, combined with slicer (as per number of record that you have).

#### politicalangel

##### New Member
Why the aversion to Pivot Table?
It's probably the most efficient method to accomplish what you are after, combined with slicer (as per number of record that you have).

I will be creating additional dashboards for averages and summaries with the data hence wanted a INDEX List formula

#### Chihiro

##### Excel Ninja
Then use something like...
Code:
``=INDEX(\$A\$2:\$A\$16,SMALL(IF((\$B\$2:\$B\$16=\$J\$2)*(\$C\$2:\$C\$16=\$J\$3)*(\$E\$2:\$E\$16=\$J\$4),ROW(\$A\$2:\$A\$16)-1),ROWS(\$A\$1:A1)))``
Confirmed as array (CTRL + SHIFT + ENTER)

Note that this is fairly expensive formula construct.
Not sure how well it will perform with size of your data.

As well, since your data validations are not set up as dependent validation, there are many combinations that will result in no match found.

To add more complexity to this. If one of the field is blank, it's going to break entire formula. You will need something like below.

Code:
``=INDEX(\$A\$2:\$A\$16,SMALL(IF(((\$B\$2:\$B\$16=\$J\$2)+(\$J\$2=""))*((\$C\$2:\$C\$16=\$J\$3)+(\$J\$3=""))*((\$E\$2:\$E\$16=\$J\$4)+(\$J\$4="")),ROW(\$A\$2:\$A\$16)-1),ROWS(\$A\$1:A1)))``
Confirmed as array (CTRL + SHIFT + ENTER)

While it is hard to guess at your final design... I'd still recommend use of pivot table.

Have hidden sheet(s) housing pivot tables.
Make sure all pviot table share same pivotcache to avoid data bloat.
You can then use that data as you see fit (pull info using formula, chart etc).