• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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)



Thanks in Advance
 

Attachments

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).
 
Top