Multiple matches in an Array (one or two criteria

[rotmanite11]

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: outer_border, width: 500"]
<tbody>[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]
</tbody>[/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: outer_border, width: 500"]
<tbody>[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]
</tbody>[/TABLE]

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


[TABLE="class: outer_border, width: 500"]
<tbody>[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]
</tbody>[/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.

 

[AhoyNC]

Try:
These are array formulas and must be entered with CTRL-SHIFT-ENTER
Copy formulas down as needed.

Excel Workbook
	A	B
1	Use	I use it to go for work
2		Family trips
3		Weekend fun
4		2nd car
5		Work
Use

Excel Workbook
	A	B	C
1	Car	Region	Color
2	Honda	North	Green
3			White
Color

Excel Workbook
	A	B	C	D	E
1	Car	Region	Own	Use	Color
2	Honda	North	Yes	I use it to go for work	Green
3	Toyota	South	No	Family trips	Grey
4	BMW	East	Yes	Weekend fun	Blue
5	Ford	North	Yes		Yellow
6	Honda	North	Yes	2nd car	White
7	Toyota	West	No	Work	Green
Sheet1

 

[rotmanite11]

WOW...Thanks.. Works like a Charm (both of them)...

Thank you.. again!

 

[AhoyNC]

You're welcome. Thanks for the feedback.