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>
<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>
</tbody>
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")
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>Thanks!
<tbody>
</tbody>
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>
| Network | Type | Show |
| ABC | Comedy | Jimmy Kimmel Live |
| ABC | Sitcom | Castle |
| ABC | Movie | Top Gun |
| CBS | Sitcom | The Good Wife |
| CBS | Sitcom | NCIS |
| CBS | News | 60 Minutes |
<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>
</tbody>
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")
<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>
<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> </tbody> | |||||||||||||||||||
| 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.
<tbody> </tbody> |
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>
<tbody> </tbody> | |||||||||||||||||||||||
<tbody>
</tbody>
