Multiple matches in an Array (one or two criteria) and list the results

  • Hi there,


    I have a question I am trying to solve. I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. I am hoping one of the Excel experts can help me out:


    The data set is something similar to the below:


    [TABLE="class: cms_table_outer_border, width: 500"]

    [tr]


    [td]

    Car

    [/td]


    [td]

    Region

    [/td]


    [td]

    Own

    [/td]


    [td]

    Use

    [/td]


    [td]

    Color

    [/td]


    [/tr]


    [tr]


    [td]

    Honda

    [/td]


    [td]

    North

    [/td]


    [td]

    Yes

    [/td]


    [td]

    I use it to go for work

    [/td]


    [td]

    Green

    [/td]


    [/tr]


    [tr]


    [td]

    Toyota

    [/td]


    [td]

    South

    [/td]


    [td]

    No

    [/td]


    [td]

    Family trips

    [/td]


    [td]

    Grey

    [/td]


    [/tr]


    [tr]


    [td]

    BMW

    [/td]


    [td]

    East

    [/td]


    [td]

    Yes

    [/td]


    [td]

    Weekend fun

    [/td]


    [td]

    Blue

    [/td]


    [/tr]


    [tr]


    [td]

    Ford

    [/td]


    [td]

    North

    [/td]


    [td]

    Yes

    [/td]


    [td][/td]


    [td]

    Yellow

    [/td]


    [/tr]


    [tr]


    [td]

    Honda

    [/td]


    [td]

    North

    [/td]


    [td]

    Yes

    [/td]


    [td]

    2nd car

    [/td]


    [td]

    White

    [/td]


    [/tr]


    [tr]


    [td]

    Toyota

    [/td]


    [td]

    West

    [/td]


    [td]

    No

    [/td]


    [td]

    Work

    [/td]


    [td]

    Green

    [/td]


    [/tr]


    [/TABLE]


    I want to be able to do following (2 separate tasks):


    Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):


    [TABLE="class: cms_table_outer_border, width: 500"]

    [tr]


    [td]

    Use

    [/td]


    [td]

    I use it to go for work

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Family trips

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Weekend fun

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    2nd car

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Work

    [/td]


    [/tr]


    [/TABLE]


    Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):



    [TABLE="class: cms_table_outer_border, width: 500"]

    [tr]


    [td]

    Car

    [/td]


    [td]

    Region

    [/td]


    [td]

    Color

    [/td]


    [/tr]


    [tr]


    [td]

    Honda

    [/td]


    [td]

    North

    [/td]


    [td]

    Green

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    White

    [/td]


    [/tr]


    [/TABLE]



    I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.


    Thanks for your help.


    Posted also on: http://www.mrexcel.com/forum/e…ray-one-two-criteria.html

  • Re: Multiple matches in an Array (one or two criteria) and list the results


    Thank you. Perfect. Appreciate your help and prompt response.


    Cheers!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!