Forum Discussion

nishantlakshya's avatar
nishantlakshya
Copper Contributor
Jul 23, 2021

Return multiple match function

Hi, 

I am building a taxonomy in excel for a financial product to define their metrics. It is similar to providing all options in a school library catalogue based on your filter. 

 

- I have 3 header: Each header is a sub info of previous header. For e.g. Header 1 (A1)= Objectives which include Business, & User Experience 

Header 2 (B1) = category which is tagged to each objective - Business and User Experience

Header 3 (D1) = subcategory which is tagged to each category 

 

I want to create a multiple match result in the output.

Input (B7 & F7): The user selects Any One Objective (it is a dropdown)

Output (A10: C15 & E10: G15): Objective & corresponding category and SubCategory automatically get filled. 

 

p.s. I do not have filter function. 

 

I have given a live example in the attached sheet. 

 

 

BI & Data Analysis
excel
Formulas and Functions

3 Replies

    • nishantlakshya's avatar
      nishantlakshya
      Copper Contributor
      Jul 24, 2021
      Thanks for the response Lz. But i am a little confused. How the response change based on the drop down in sheet 2? Can you please elaborate, and give me some key steps to repeat the process?
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 24, 2021

        nishantlakshya 

         

        You can forget/delete Sheet2. I created it just to copy/paste your expected result when 'User Experience' is selected in Sheet1 B7 (allowed me to compare what you expect and what the query returns)

         

        How to:

        - Select either Business or User Experience in Sheet1 B7

        - Right-click somewhere in the blue Table under QueryResult > Refresh

         

        Note:

        The 1st query Refresh within an Excel session takes more time (to load the .Net Framework) than the next Refreshes

Resources