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]

Correction, I looked at my formula and it doesn't work either... Would adding column L help?

[TABLE="width: 837"]
<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]
[TD]L</SPAN>[/TD]
[/TR]
[TR]
[TD]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]
[TD][/TD]
[/TR]
[TR]
[TD]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]
[TD]1.3</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]Type 1:</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]a</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]
[TD]2.3</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]Type 2:</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]b</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]
[TD]3.3</SPAN>[/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]Type 3:</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</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]
[TD]1.3</SPAN>[/TD]
[/TR]
[TR]
[TD]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]
[TD]2.3</SPAN>[/TD]
[/TR]
[TR]
[TD]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]
[TD]3.3</SPAN>[/TD]
[/TR]
[TR]
[TD]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]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]Beta</SPAN>[/TD]
[TD]Beta</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/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]Beta</SPAN>[/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 span=5></COLGROUP>[/TABLE]

 

[circledchicken]

Why do you need to have a complex single formula solution? Can you not split it into parts? If you must have a one-cell solution, would a VBA solution suffice or does it have to be formula-only?

 

[bk1702]

It needs to be able to keep searching for a result that works, breaking it into the 3 cells stops it looking for the solution that matches all conditions. I'm open to options, hidden cells, VBA, adding a coloumn or two, etc.

This is just a small sample of the actual data I'm trying to get to match, see my other thread.

Thank you for all of your help with this!!

 

[bk1702]

Or is this what your trying to find out - i.e. where 1 of them can be either you want to get the name that would make all three match?

Yes, in any order

 

[circledchicken]

Hello again,

I think the following should work with your sample dataset:

Add this to a VBA module:

Function FullMatch(iRng As Range, oRng As Range) As Variant

' ---------------------------
' Find a name that matches
' all three cuts, else show
' FALSE
' ---------------------------


Dim vA          As Variant
Dim vB          As Variant
Dim vC          As Variant
Dim astrA()     As String
Dim oDictA      As Object
Dim oDictB      As Object
Dim i           As Long
Dim j           As Long


With Application


    vA = .Transpose(iRng) ' the 3 cuts we want to match
    vB = oRng ' the match table
    vC = oRng.Offset(0, 2).Resize(, oRng.Columns.Count - 2) ' the match table without names and cuts
        
    ' build an array with the three cuts we want to match
    ReDim astrA(1 To UBound(vA, 1))
    For i = 1 To UBound(vA, 1)
        astrA(i) = Join(.Index(vA, i)) '
    Next i
    
    ' get a unique set of items from the table to match with
    ' and a unique set of names
    Set oDictA = CreateObject("scripting.dictionary")
    Set oDictB = CreateObject("scripting.dictionary")
    On Error Resume Next
        For i = 1 To UBound(vB, 1)
            oDictA.Add Key:=CStr(Join(.Index(vB, i), "|")), Item:=Join(.Index(vC, i))
            oDictB.Add Key:=CStr(vB(i, 1)), Item:=0
        Next i
    On Error GoTo 0
                            
    vA = oDictA.keys
    vB = oDictA.Items
    For i = 1 To UBound(astrA)
        If IsError(.Match(astrA(i), .Index(vB, 0), 0)) Then
            ' if any of the three cuts isn't found in the table
            ' then return FALSE
            FullMatch = False
        Else
            ' in the unique list of table names
            ' add one to the name(s) that match
            For j = 0 To UBound(vA)
                If (astrA(i) = oDictA.Item(vA(j))) Then
                    vC = Split(vA(j), "|")(0)
                    oDictB.Item(vC) = oDictB.Item(vC) + 1
                End If
            Next j
        End If
    Next i
        
    ' if there are 3 matches display the result,
    ' else show FALSE
    If .Max(.Index(oDictB.Items, 0)) < UBound(astrA) Then
        FullMatch = False
    Else
    FullMatch = .Index(.Index(oDictB.keys, 0), _
                .Match(UBound(astrA), .Index(oDictB.Items, 0), 0))
    End If
    
End With


End Function

Use it in the sheet like this:
Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]2[/TH]
[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]
[/TR]
[TR]
[TH]3[/TH]
[TD][/TD]
[TD]CUT 1[/TD]
[TD]CUT 2[/TD]
[TD]CUT 3[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Type 1:[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Type 2:[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Type 3:[/TD]
[TD]c[/TD]
[TD]e[/TD]
[TD]e[/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD][/TD]
[TD]Beta[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
C9	=FullMatch(C4:E6,G3:K8)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
I'm not sure what the role of column H is though? This code above assumes column H will always say the same thing as per the sample.

 

[bk1702]

Thanks for all your help!

I could only get the VBA to work in a brand new file... and I have no idea how to go about changing it to fit my actual data.

This is the same thing just with way more data and you'll see where column H comes into play:
http://www.mrexcel.com/forum/showthread.php?647606-A-Challenge-multiple-lines-of-data-to-match-and-with-conditions

Also, my attempts at the formula don't work in the post for multiple items.

Thanks again!

 

[Marcelo Branco]

Maybe something like this

B C D E F G H I J K
[TABLE="width: 585"]
<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" width=84><TBODY>[TR]
[TD="class: xl66, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 84, bgcolor: transparent"] [/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: xl67, bgcolor: transparent"]Type 3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 1[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 2[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/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"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl67, bgcolor: transparent"]d[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Type 1:[/TD]
[TD="class: xl65, bgcolor: transparent"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]b[/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"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl67, bgcolor: transparent"]e[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Type 2:[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/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"]b[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl67, bgcolor: transparent"]e[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Type 3:[/TD]
[TD="class: xl65, bgcolor: transparent"]e[/TD]
[TD="class: xl65, bgcolor: transparent"]d[/TD]
[TD="class: xl65, bgcolor: transparent"]e[/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"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]b[/TD]
[TD="class: xl67, bgcolor: transparent"]c[/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"]Beta[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]b[/TD]
[TD="class: xl67, bgcolor: transparent"]e[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Cut[/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"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl67, bgcolor: transparent"]e[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]All Match[/TD]
[TD="class: xl69, bgcolor: transparent"]Alpha[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]




In C8 enter the Cut of interes (CUT 3 in the example)

In C9 this array formula

=INDEX($G$3:$G$8,MATCH(TRUE,MMULT(--(IF($H$3:$H$8=$C$8,$I$3:$K$8)=TRANSPOSE(INDEX($C$4:$E$6,0,MATCH($C$8,$C$3:$E$3,0)))),{1;1;1})=3,0))

confirm with Ctrl+Shift+Enter

M.

 

[bk1702]

I tried it and changed the data and it should have returned Beta instead of Alpha for this case...
[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]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]1.3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Type 1:
[/TD]
[TD]f
[/TD]
[TD]a
[/TD]
[TD]c
[/TD]
[TD][/TD]
[TD]Alpha
[/TD]
[TD]CUT 3
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[TD]2.3
[/TD]
[TD][/TD]
[TD]Alpha
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Type 2:
[/TD]
[TD]e
[/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD][/TD]
[TD]Alpha
[/TD]
[TD]CUT 3
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[TD]3.3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Type 3:
[/TD]
[TD]d
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD][/TD]
[TD]Beta
[/TD]
[TD]CUT 3
[/TD]
[TD]f
[/TD]
[TD]e
[/TD]
[TD]d
[/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]c
[/TD]
[TD]b
[/TD]
[TD]a
[/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]a
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]3.3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

This is what I'm trying to get to work with what you had, but it's not working at all... maybe it'll help?
=INDEX(G3:G8,MATCH(1,(INDEX((INDEX(G3:G8,MATCH(C4&C5&C6,I3:I8&J3:J8&K3:K8,0),0))=(INDEX(G3:G8,MATCH(D4&D5&D6,I3:I8&J3:J8&K3:K8,0),0))*(INDEX(G3:G8,MATCH(E4&E5&E6=I3:I8&J3:J8&K3:K8,0),0))=(INDEX(G3:G8,MATCH(D4&D5&D6,I3:I8&J3:J8&K3:K8,0),0)),0),0),0),0)


This is the ultimate goal for the data in the "simplified" formula:

http://www.mrexcel.com/forum/showth...le-lines-of-data-to-match-and-with-conditions

 

[bk1702]

This is the ultimate goal for the data in the "simplified" formula:

http://www.mrexcel.com/forum/showth...le-lines-of-data-to-match-and-with-conditions

 

[Marcelo Branco]

For me the formula worked, ie, returned Beta...
[TABLE="width: 585"]
<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" width=84><TBODY>[TR]
[TD="class: xl67, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 77, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 84, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, 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: xl66, bgcolor: transparent"]Type 3[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 1[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 2[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/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"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl66, bgcolor: transparent"]d[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Type 1:[/TD]
[TD="class: xl65, bgcolor: transparent"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/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"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl66, bgcolor: transparent"]e[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Type 2:[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl65, bgcolor: transparent"]b[/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"]b[/TD]
[TD="class: xl65, bgcolor: transparent"]c[/TD]
[TD="class: xl66, bgcolor: transparent"]e[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Type 3:[/TD]
[TD="class: xl65, bgcolor: transparent"]e[/TD]
[TD="class: xl65, bgcolor: transparent"]d[/TD]
[TD="class: xl65, bgcolor: transparent"]a[/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"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]b[/TD]
[TD="class: xl66, bgcolor: transparent"]c[/TD]
[/TR]
[TR]
[TD="class: xl67, 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"]Beta[/TD]
[TD="class: xl65, bgcolor: transparent"]CUT 3[/TD]
[TD="class: xl65, bgcolor: transparent"]a[/TD]
[TD="class: xl65, bgcolor: transparent"]b[/TD]
[TD="class: xl66, bgcolor: transparent"]e[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Cut[/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"]c[/TD]
[TD="class: xl65, bgcolor: transparent"]b[/TD]
[TD="class: xl66, bgcolor: transparent"]a[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]All Match[/TD]
[TD="class: xl69, bgcolor: transparent"]Beta[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]