Formula Help, CSE & Match with Multiple Criteria

[bk1702]

How do I get the value that matches for each of these?

Desired result in C9 is "Alpha". This is also a CSE formula.

{=IF(AND(INDEX(G3:G5,MATCH(E3&E4&E5&E6,H3:H5&I3:I5&J3:J5&K3:K5,0))=INDEX(G3:G5,MATCH(E3&D4&D5&D6,H3:H5&I3:I5&J3:J5&K3:K5,0)),INDEX(G3:G5,MATCH(E3&C4&C5&C6,H3:H5&I3:I5&J3:J5&K3:K5,0))=INDEX(G3:G5,MATCH(E3&D4&D5&D6,H3:H5&I3:I5&J3:J5&K3:K5,0))),"How do I show the value that matches for each of these?","false")}


Thanks for any help you can provide!

[TABLE="class: grid, width: 773"]
<TBODY>[TR]
[TD][/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[TD]F</SPAN>[/TD]
[TD]G</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[TD]I</SPAN>[/TD]
[TD]J</SPAN>[/TD]
[TD]K</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name</SPAN>[/TD]
[TD]# Cuts</SPAN>[/TD]
[TD]Type 1</SPAN>[/TD]
[TD]Type 2</SPAN>[/TD]
[TD]Type 3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>[/TD]
[TD][/TD]
[TD]CUT 1</SPAN>[/TD]
[TD]CUT 2</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]d</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>[/TD]
[TD]Type 1:</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>[/TD]
[TD]Type 2:</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>[/TD]
[TD]Type 3:</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[TD]d</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 5"]How do I show the value that matches for each of these?</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL span=4></COLGROUP>[/TABLE]

 

[bk1702]

Most of the time it works... Try it for the case below. It seems to make the connection even if two match.. In the case below two match for both but neither match all three, yet it still returns 'Beta'


[TABLE="width: 965"]
<TBODY>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]# Cuts[/TD]
[TD]Type 1[/TD]
[TD]Type 2[/TD]
[TD]Type 3[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]CUT 1[/TD]
[TD]CUT 2[/TD]
[TD]CUT 3[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]left[/TD]
[TD]cat[/TD]
[TD]car[/TD]
[TD]1.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Type 1:[/TD]
[TD]left[/TD]
[TD]up[/TD]
[TD]down[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]right[/TD]
[TD]dog[/TD]
[TD]truck[/TD]
[TD]2.3[/TD]
[TD][/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Type 2:[/TD]
[TD]cat[/TD]
[TD]horse[/TD]
[TD]rabbit[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]up[/TD]
[TD]horse[/TD]
[TD]bike[/TD]
[TD]3.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Type 3:[/TD]
[TD]car[/TD]
[TD]bike[/TD]
[TD]bike[/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]left[/TD]
[TD]cat[/TD]
[TD]car[/TD]
[TD]1.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]right[/TD]
[TD]dog[/TD]
[TD]truck[/TD]
[TD]2.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]CUT 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]down[/TD]
[TD]rabbit[/TD]
[TD]bike[/TD]
[TD]3.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL span=5><COL span=2></COLGROUP>[/TABLE]


Thank you for all of your help!!

 

[Marcelo Branco]

It seems it worked...

[TABLE="width: 648"]
<COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" span=9 width=77><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" span=2 width=84><TBODY>[TR]
[TD="class: xl66, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 84"] [/TD]
[TD="class: xl67, width: 84"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Name[/TD]
[TD="class: xl65, bgcolor: transparent"]# Cuts[/TD]
[TD="class: xl65, bgcolor: transparent"]Type 1[/TD]
[TD="class: xl65, bgcolor: transparent"]Type 2[/TD]
[TD="class: xl65, bgcolor: transparent"]Type 3[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl65"]CUT 1[/TD]
[TD="class: xl65"]CUT 2[/TD]
[TD="class: xl65"]CUT 3[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Alpha[/TD]
[TD="class: xl65"]CUT 3[/TD]
[TD="class: xl65"]left[/TD]
[TD="class: xl65"]cat[/TD]
[TD="class: xl65"]car[/TD]
[TD="class: xl67"]1.3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Type 1:[/TD]
[TD="class: xl65, bgcolor: transparent"]left[/TD]
[TD="class: xl65, bgcolor: transparent"]up[/TD]
[TD="class: xl65, bgcolor: transparent"]down[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Alpha[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"]right[/TD]
[TD="class: xl65, bgcolor: transparent"]dog[/TD]
[TD="class: xl65, bgcolor: transparent"]truck[/TD]
[TD="class: xl67, bgcolor: transparent"]2.3[/TD]
[/TR]
[TR]
[TD="class: xl66"]Type 2:[/TD]
[TD="class: xl65"]cat[/TD]
[TD="class: xl65"]horse[/TD]
[TD="class: xl65"]rabbit[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Alpha[/TD]
[TD="class: xl65"]CUT 3[/TD]
[TD="class: xl65"]up[/TD]
[TD="class: xl65"]horse[/TD]
[TD="class: xl65"]bike[/TD]
[TD="class: xl67"]3.3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Type 3:[/TD]
[TD="class: xl65, bgcolor: transparent"]car[/TD]
[TD="class: xl65, bgcolor: transparent"]bike[/TD]
[TD="class: xl65, bgcolor: transparent"]bike[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Beta[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"]left[/TD]
[TD="class: xl65, bgcolor: transparent"]cat[/TD]
[TD="class: xl65, bgcolor: transparent"]car[/TD]
[TD="class: xl67, bgcolor: transparent"]1.3[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Beta[/TD]
[TD="class: xl65"]CUT 3[/TD]
[TD="class: xl65"]right[/TD]
[TD="class: xl65"]dog[/TD]
[TD="class: xl65"]truck[/TD]
[TD="class: xl67"]2.3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Beta[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"]down[/TD]
[TD="class: xl65, bgcolor: transparent"]rabbit[/TD]
[TD="class: xl65, bgcolor: transparent"]bike[/TD]
[TD="class: xl67, bgcolor: transparent"]3.3[/TD]
[/TR]
[TR]
[TD="class: xl68"] [/TD]
[TD="class: xl69"]Beta[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[/TR]
</TBODY>[/TABLE]

 

[Marcelo Branco]

Worked too changing C8 to CUT 1 and also H3 to CUT 1

[TABLE="width: 648"]
<COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" span=9 width=77><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" span=2 width=84><TBODY>[TR]
[TD="class: xl66, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 77"] [/TD]
[TD="class: xl65, width: 84"] [/TD]
[TD="class: xl67, width: 84"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Name[/TD]
[TD="class: xl65, bgcolor: transparent"]# Cuts[/TD]
[TD="class: xl65, bgcolor: transparent"]Type 1[/TD]
[TD="class: xl65, bgcolor: transparent"]Type 2[/TD]
[TD="class: xl65, bgcolor: transparent"]Type 3[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl65"]CUT 1[/TD]
[TD="class: xl65"]CUT 2[/TD]
[TD="class: xl65"]CUT 3[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Alpha[/TD]
[TD="class: xl65"]CUT 1[/TD]
[TD="class: xl65"]left[/TD]
[TD="class: xl65"]cat[/TD]
[TD="class: xl65"]car[/TD]
[TD="class: xl67"]1.3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Type 1:[/TD]
[TD="class: xl65, bgcolor: transparent"]left[/TD]
[TD="class: xl65, bgcolor: transparent"]up[/TD]
[TD="class: xl65, bgcolor: transparent"]down[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Alpha[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"]right[/TD]
[TD="class: xl65, bgcolor: transparent"]dog[/TD]
[TD="class: xl65, bgcolor: transparent"]truck[/TD]
[TD="class: xl67, bgcolor: transparent"]2.3[/TD]
[/TR]
[TR]
[TD="class: xl66"]Type 2:[/TD]
[TD="class: xl65"]cat[/TD]
[TD="class: xl65"]horse[/TD]
[TD="class: xl65"]rabbit[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Alpha[/TD]
[TD="class: xl65"]CUT 3[/TD]
[TD="class: xl65"]up[/TD]
[TD="class: xl65"]horse[/TD]
[TD="class: xl65"]bike[/TD]
[TD="class: xl67"]3.3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Type 3:[/TD]
[TD="class: xl65, bgcolor: transparent"]car[/TD]
[TD="class: xl65, bgcolor: transparent"]bike[/TD]
[TD="class: xl65, bgcolor: transparent"]bike[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Beta[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"]left[/TD]
[TD="class: xl65, bgcolor: transparent"]cat[/TD]
[TD="class: xl65, bgcolor: transparent"]car[/TD]
[TD="class: xl67, bgcolor: transparent"]1.3[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Beta[/TD]
[TD="class: xl65"]CUT 3[/TD]
[TD="class: xl65"]right[/TD]
[TD="class: xl65"]dog[/TD]
[TD="class: xl65"]truck[/TD]
[TD="class: xl67"]2.3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Beta[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"]down[/TD]
[TD="class: xl65, bgcolor: transparent"]rabbit[/TD]
[TD="class: xl65, bgcolor: transparent"]bike[/TD]
[TD="class: xl67, bgcolor: transparent"]3.3[/TD]
[/TR]
[TR]
[TD="class: xl68"] [/TD]
[TD="class: xl69"]Alpha[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl70"] [/TD]
[/TR]
</TBODY>[/TABLE]

 

[bk1702]

Maybe this will help clarify what I'm looking for:

C4:C6 matches a cut in both Alpha and Beta
D4:D6 matches a cut in Alpha only
E4:E6 matches a cut in Beta only
Looking for an error or "Does not Exist" for result for this data set

For a response of Beta it would mean that:
C4:C6 matches a cut in both Alpha and Beta
D4:D6 matches a cut in Alpha and Beta only
E4:E6 matches a cut in Beta only

The Name is unique for each item.
[TABLE="width: 1028"]
<TBODY>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]# Cuts[/TD]
[TD]Direction[/TD]
[TD]Animal[/TD]
[TD]Vehicle[/TD]
[TD]Sequence of Cut (1.2 is 1of2, 2.2 is 2of2, etc) No Specific Order[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]CUT 1[/TD]
[TD]CUT 2[/TD]
[TD]CUT 3[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]left[/TD]
[TD]cat[/TD]
[TD]car[/TD]
[TD]1.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Direction[/TD]
[TD]left[/TD]
[TD]up[/TD]
[TD]down[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]right[/TD]
[TD]dog[/TD]
[TD]truck[/TD]
[TD]2.3[/TD]
[TD][/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Animal[/TD]
[TD]cat[/TD]
[TD]horse[/TD]
[TD]rabbit[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]up[/TD]
[TD]horse[/TD]
[TD]bike[/TD]
[TD]3.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Vehicle[/TD]
[TD]car[/TD]
[TD]bike[/TD]
[TD]bike[/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]left[/TD]
[TD]cat[/TD]
[TD]car[/TD]
[TD]1.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]right[/TD]
[TD]dog[/TD]
[TD]truck[/TD]
[TD]2.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]CUT 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]down[/TD]
[TD]rabbit[/TD]
[TD]bike[/TD]
[TD]3.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL span=4><COL><COL span=2></COLGROUP>[/TABLE]

 

[Marcelo Branco]

Maybe this will help clarify what I'm looking for:

C4:C6 matches a cut in both Alpha and Beta
D4:D6 matches a cut in Alpha only
E4:E6 matches a cut in Beta only
Looking for an error or "Does not Exist" for result for this data set

I think i misunderstood what you needed.

My formula tries to match considering the CUT #.

So C4:C6 and D4:D6 don't match Alpha and Beta because they are from
different cuts (Cuts 1 and 2 respectively).

The formula tries to match the values in columns I J K with only the values corresponding the same Cut in range C4:E6.

As in your data sample there are only values of Cut 3 the formula compares these values with E4:E6 - those of the same cut, ie, Cut 3.

M.
.

 

[bk1702]

I'm sorry for the confusion. I could have labeled the columns better... The 'CUT 3' is my way of indicating 3 cuts, as other data (not in the sample) have between 1 and 6 different cuts applied to one Name. The name is really the item that ties each of the cuts together.

I appreciate all the time you've taken to help, and I think it's helping me to try different things. Thanks!

 

[bk1702]

So if I manipulate the data like so I get pretty close with the formula below. Although it comes back with "0" instead of "Beta". Thoughts?

=INDEX((INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$J$3:$L$4)=TRANSPOSE(INDEX($C$4:$C$6,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0))=INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$M$3:$O$4)=TRANSPOSE(INDEX($C$7:$C$9,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0)))*(INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$J$3:$L$4)=TRANSPOSE(INDEX($C$4:$C$6,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0))=INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$P$3:$R$4)=TRANSPOSE(INDEX($C$10:$C$12,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0)))*(INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$M$3:$O$4)=TRANSPOSE(INDEX($C$7:$C$9,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0))=INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$P$3:$R$4)=TRANSPOSE(INDEX($C$10:$C$12,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0))),0)
[TABLE="width: 1297"]
<TBODY>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]# Cuts[/TD]
[TD]Sequence of Cut (1.2 is 1of2, 2.2 is 2of2, etc) No Specific Order[/TD]
[TD]Direction[/TD]
[TD]Animal[/TD]
[TD]Vehicle[/TD]
[TD]Direction[/TD]
[TD]Animal[/TD]
[TD]Vehicle[/TD]
[TD]Direction[/TD]
[TD]Animal[/TD]
[TD]Vehicle[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1.3[/TD]
[TD][/TD]
[TD]CUT 3[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]1.3[/TD]
[TD]left[/TD]
[TD]cat[/TD]
[TD]car[/TD]
[TD]right[/TD]
[TD]dog[/TD]
[TD]truck[/TD]
[TD]up[/TD]
[TD]horse[/TD]
[TD]bike[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Direction[/TD]
[TD]left[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]1.3[/TD]
[TD]left[/TD]
[TD]cat[/TD]
[TD]car[/TD]
[TD]right[/TD]
[TD]rabbit[/TD]
[TD]truck[/TD]
[TD]down[/TD]
[TD]rabbit[/TD]
[TD]bike[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Animal[/TD]
[TD]cat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Vehicle[/TD]
[TD]car[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]right[/TD]
[TD][/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD="colspan: 12"]. =INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$J$3:$L$4)=TRANSPOSE(INDEX($C$4:$C$6,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]rabbit[/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD="colspan: 12"]. =INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$M$3:$O$4)=TRANSPOSE(INDEX($D$4:$D$6,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]truck[/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD="colspan: 12"]. =INDEX($G$3:$G$4,MATCH(TRUE,MMULT(--(IF($H$3:$H$4=$E$3,$P$3:$R$4)=TRANSPOSE(INDEX($E$4:$E$6,0,MATCH($E$3,$E$3,0)))),{1;1;1})=3,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]down[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]rabbit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]bike[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL><COL><COL span=3><COL><COL span=5></COLGROUP>[/TABLE]

 

[bk1702]

Ok. that formula doesn't work either...

-Throwing hands up

 

[Marcelo Branco]

Hi,

I'll take a look later, but i'm a little bit confused. Could you, please, explain again what are you trying to match (with what...)?

M.

 

[bk1702]

[TABLE="class: grid, width: 1015"]
<TBODY>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]# Cuts
[/TD]
[TD]Direction
[/TD]
[TD]Animal
[/TD]
[TD]Vehicle
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]CUT 1 of 3 OR Cut 2 of 3 OR Cut 3 of 3
[/TD]
[TD]CUT 1 of 3 OR Cut 2 of 3 OR Cut 3 of 3
[/TD]
[TD]CUT 1 of 3 OR Cut 2 of 3 OR Cut 3 of 3
[/TD]
[TD][/TD]
[TD]Alpha
[/TD]
[TD]CUT 1 of 3
[/TD]
[TD]left
[/TD]
[TD]cat
[/TD]
[TD]car
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Direction
[/TD]
[TD]left
[/TD]
[TD]down
[/TD]
[TD]right
[/TD]
[TD][/TD]
[TD]Alpha
[/TD]
[TD]CUT 2 of 3
[/TD]
[TD]right
[/TD]
[TD]dog
[/TD]
[TD]truck
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Animal
[/TD]
[TD]cat
[/TD]
[TD]rabbit
[/TD]
[TD]dog
[/TD]
[TD][/TD]
[TD]Alpha
[/TD]
[TD]Cut 3 of 3
[/TD]
[TD]up
[/TD]
[TD]horse
[/TD]
[TD]bike
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Vehicle
[/TD]
[TD]car
[/TD]
[TD]bike
[/TD]
[TD]bike
[/TD]
[TD][/TD]
[TD]Beta
[/TD]
[TD]CUT 1 of 3
[/TD]
[TD]left
[/TD]
[TD]cat
[/TD]
[TD]car
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta
[/TD]
[TD]CUT 2 of 3
[/TD]
[TD]right
[/TD]
[TD]dog
[/TD]
[TD]bike
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Correct Response would be: "Beta"[/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta
[/TD]
[TD]Cut 3 of 3
[/TD]
[TD]down
[/TD]
[TD]rabbit
[/TD]
[TD]bike
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD="colspan: 10"]Each of these: C4:C6, D4:D6, E4:E6 must match a row of data with the same name
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD="colspan: 10"]C4:E6 are inputs that are to line up with the data for one name, I'm trying to get the name that matches all 3 cuts.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD="colspan: 10"]The 'cuts' can be applied in any order
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD="colspan: 10"]Another way to arrange the data would be (this would take some work to reorganize it all so I'd like to avoid if possible):
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cut 1
[/TD]
[TD][/TD]
[TD="colspan: 3"]Cut 2
[/TD]
[TD][/TD]
[TD="colspan: 3"]Cut 3
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]Name
[/TD]
[TD]# Cuts
[/TD]
[TD]Direction
[/TD]
[TD]Animal
[/TD]
[TD]Vehicle
[/TD]
[TD][/TD]
[TD]Direction
[/TD]
[TD]Animal
[/TD]
[TD]Vehicle
[/TD]
[TD][/TD]
[TD]Direction
[/TD]
[TD]Animal
[/TD]
[TD]Vehicle
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]Alpha
[/TD]
[TD]CUT 1 of 3
[/TD]
[TD]left
[/TD]
[TD]cat
[/TD]
[TD]car
[/TD]
[TD]CUT 2 of 3
[/TD]
[TD]right
[/TD]
[TD]dog
[/TD]
[TD]truck
[/TD]
[TD]Cut 3 of 3
[/TD]
[TD]up
[/TD]
[TD]horse
[/TD]
[TD]bike
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]Beta
[/TD]
[TD]CUT 1 of 3
[/TD]
[TD]left
[/TD]
[TD]cat
[/TD]
[TD]car
[/TD]
[TD]CUT 2 of 3
[/TD]
[TD]right
[/TD]
[TD]dog
[/TD]
[TD]bike
[/TD]
[TD]Cut 3 of 3
[/TD]
[TD]down
[/TD]
[TD]rabbit
[/TD]
[TD]bike
[/TD]
[/TR]
</TBODY>[/TABLE]