Index & Match Function with Multiple References and Multiple Solutions

[tttmeyer]

Hi,

I'm attempting to find a solution to the overcome the problem of having multiple solutions when using an array function with index and match functions. To start, here is a data set example (A1:C7):

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-weight: 700; border: 0.5pt solid windowtext; }.xl64 { border: 0.5pt solid windowtext; }</style> [TABLE="width: 265"]
<colgroup><col style="width:65pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:4266; width:100pt" span="2" width="100"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]Network[/TD]
[TD="class: xl63, width: 100"]Type[/TD]
[TD="class: xl63, width: 100"]Show[/TD]
[/TR]
[TR]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl64"]Comedy[/TD]
[TD="class: xl64"]Jimmy Kimmel Live[/TD]
[/TR]
[TR]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl64"]Sitcom[/TD]
[TD="class: xl64"]Castle[/TD]
[/TR]
[TR]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl64"]Movie[/TD]
[TD="class: xl64"]Top Gun[/TD]
[/TR]
[TR]
[TD="class: xl64"]CBS[/TD]
[TD="class: xl64"]Sitcom[/TD]
[TD="class: xl64"]The Good Wife[/TD]
[/TR]
[TR]
[TD="class: xl64"]CBS[/TD]
[TD="class: xl64"]Sitcom[/TD]
[TD="class: xl64"]NCIS[/TD]
[/TR]
[TR]
[TD="class: xl64"]CBS[/TD]
[TD="class: xl64"]News[/TD]
[TD="class: xl64"]60 Minutes[/TD]
[/TR]
</tbody>[/TABLE]

Below is the new form I'm seeking to create using the following function (or relevant variation) in cells B14:E15. This is the function in cell B14.

=IFERROR(INDEX($C$2:$C$7,MATCH(1,($A$2:$A$7=$A14)*($B$2:$B$7=B$13),0)),"N/A")


[TABLE="width: 527"]
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD] <style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-weight: 700; border: 0.5pt solid windowtext; }.xl64 { border: 0.5pt solid windowtext; }</style> [TABLE="width: 395"]
<colgroup><col style="width:65pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:4266; width:100pt" span="2" width="100"> <col style="width:65pt" span="2" width="65"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]Network
[/TD]
[TD="class: xl63, width: 100"]Comedy
[/TD]
[TD="class: xl63, width: 100"]Sitcom[/TD]
[TD="class: xl63, width: 65"]Movie[/TD]
[TD="class: xl63, width: 65"]News[/TD]
[/TR]
[TR]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl64"]Jimmy Kimmel Live
[/TD]
[TD="class: xl64"]Castle[/TD]
[TD="class: xl64"]Top Gun[/TD]
[TD="class: xl64"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"]CBS[/TD]
[TD="class: xl64"]N/A
[/TD]
[TD="class: xl64"]The Good Wife[/TD]
[TD="class: xl64"]N/A[/TD]
[TD="class: xl64"]60 Minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The formula works great except in the cases where I have multiple solutions,
such as Row 6 of the data set. How do I pull in information for Row 6 as well (and
other solution Rows)? I'm guessing I need to use the concatenate function for a single cell
solution or create a second set of Type cells to pull the next solution, but I'm not sure
how to do either. Any suggestions?

Ultimately, I'd like the final product to look like this like the chart below. See cell C3.

[TABLE="width: 395"]
<tbody>[TR]
[TD="class: xl63, width: 65"]Network
[/TD]
[TD="class: xl63, width: 100"]Comedy
[/TD]
[TD="class: xl63, width: 100"]Sitcom
[/TD]
[TD="class: xl63, width: 65"]Movie[/TD]
[TD="class: xl63, width: 65"]News[/TD]
[/TR]
[TR]
[TD="class: xl64"]ABC[/TD]
[TD="class: xl64"]Jimmy Kimmel Live
[/TD]
[TD="class: xl64"]Castle
[/TD]
[TD="class: xl64"]Top Gun[/TD]
[TD="class: xl64"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"]CBS[/TD]
[TD="class: xl64"]N/A
[/TD]
[TD="class: xl64"]The Good Wife, NCIS
[/TD]
[TD="class: xl64"]N/A[/TD]
[TD="class: xl64"]60 Minutes[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks!

[TABLE="width: 353"]
<tbody>[TR]
[TD][TABLE="width: 353"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]