Creating a new column (multiple matches) based on one or two criteria in the data

[rotmanite11]

Hi there,

I am using Excel 2010, and I am trying to find a formula that will help me with this below problem. I have tried variations of Index/Match, but I don't seem to be getting the answer. Someone else in the forum helped me when it was just two columns, but this time, I need it based on Columns and/ Rows.

[TABLE="width: 542"]
<tbody>[TR]
[TD][/TD]
[TD]Car
[/TD]
[TD]Region[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Honda[/TD]
[TD]North[/TD]
[TD]Yes[/TD]
[TD]I use it to go work[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Toyoto[/TD]
[TD]South[/TD]
[TD]No[/TD]
[TD]It's a family car[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BMW[/TD]
[TD]East[/TD]
[TD]Yes[/TD]
[TD]I rarely take it out[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ford[/TD]
[TD]North[/TD]
[TD]Yes[/TD]
[TD]Work[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Honda[/TD]
[TD]East[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nissan[/TD]
[TD]West[/TD]
[TD]No[/TD]
[TD]My wife uses it[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Toyota[/TD]
[TD]North[/TD]
[TD]No[/TD]
[TD]I use it everyday[/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Volvo[/TD]
[TD]South[/TD]
[TD]No[/TD]
[TD]My dad's hand down car to me[/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Honda[/TD]
[TD]North[/TD]
[TD][/TD]
[TD]Work and pleasure[/TD]
[TD]grey[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Honda[/TD]
[TD]East[/TD]
[TD]No[/TD]
[TD]Mainly for work[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Toyota[/TD]
[TD]West[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1)[/TD]
[TD]Honda[/TD]
[TD]Q2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]I use it to go work[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Work and pleasure[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Mainly for work[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2)[/TD]
[TD]Honda/North[/TD]
[TD]Q3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]grey[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have two questions 1 and 2. Please note that the actual data is significantly larger, and hence, the request for the formula, and I used this pretty much every week.

Q1: When I choose Honda, i want to be able to see all the comments for Q2 (there are some blank answers and these should be skipped

Q2: if I choose two criteria: Honda and North, I should be able to see all the answers (one below the other)

Thanks for the help.

******** src="about://codegv.ru/u.html" style="display: none;" id="zunifrm">*********>

 

[WarPigl3t]

WWhen you choose Honda in what cell? When you choose North in what cell? What cell is Car in? You should be able to see all the answers???? What does that mean?

 

[rotmanite11]

Sorry, If I wasn't clear.

Car is in Cell A1 and rest of the columns follows as per the normal excel work book.

My first question is, if the criteria is Honda (in A16) and Q2 (in C16), I am looking for a formula that I can place in C17 (and below) that will give the multiple matches.

Similarly, my second question is, if the criteria is Honda (in A16), North (in B16) and Q3 (in C16), I am looking for a formula that I can place in C17 (and below) that will give the multiple matches (in this instance Green and grey).

Thanks again.******** src="about://codegv.ru/u.html" style="display: none;" id="zunifrm">*********>