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:

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

<tbody>
</tbody>

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)):

Use	I use it to go for work
	Family trips
	Weekend fun
	2nd car
	Work

<tbody>
</tbody>

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

Car	Region	Color
Honda	North	Green
		White

<tbody>
</tbody>

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.