1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Excel Dashboards' started by politicalangel, Aug 9, 2018.

  1. politicalangel

    politicalangel New Member

    Messages:
    22
    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

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,824
    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).
  3. politicalangel

    politicalangel New Member

    Messages:
    22

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

    Chihiro Excel Ninja

    Messages:
    4,824
    Then use something like...
    Code (vb):
    =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 (vb):
    =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).
    Thomas Kuriakose likes this.

Share This Page