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"]
Car
Region
Own
Use
Color
Honda
North
Yes
I use it to go for work
Green
Toyota
South
No
Family trips
Grey
BMW
East
Yes
Weekend fun
Blue
Ford
North
Yes
Yellow
Honda
North
Yes
2nd car
White
Toyota
West
No
Work
Green
[/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"]
Use
I use it to go for work
Family trips
Weekend fun
2nd car
Work
[/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"]
Car
Region
Color
Honda
North
Green
White
[/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