Returning value when multiple criteria are met.

[max klein]

I have 2 spreadsheets where 1 has partial, already selected data from the other.

I want to get the value of column G when 5 criteria are met. In other words. I need to to look at the 5 other columns in that row and find the same combination in the other spreadsheet and return the value. Its like an If function but instead of returning value if a criteria isnt met. I need it to search every row and then return the value when all 5 are met.

Can someone help

 

[Herbiec09]

I have 2 spreadsheets where 1 has partial, already selected data from the other.

I want to get the value of column G when 5 criteria are met. In other words. I need to to look at the 5 other columns in that row and find the same combination in the other spreadsheet and return the value. Its like an If function but instead of returning value if a criteria isnt met. I need it to search every row and then return the value when all 5 are met.

Can someone help

How about concatenating the values on the sheet you are looking from as well as on the sheet where you want the results and then doing a lookup or if based on the concatenated values?

Would also be good if you were able to upload some dummy data on to google docs that people could look at help explain the problem better.

Simply H

 

[max klein]

Underlying	Expiration Date	Strike	Call/Put	Series Delta (End of Day)					Underlying	Expiration Date	Strike	Call/Put	Series Delta (End of Day)
AMZN	2013412	250.00	Call						AMZN	20130412	255	Put	-0.24
AMZN	2013412	250.00	Call						AMZN	20130412	260	Call	-0.01
AMZN	2013412	255.00	Call						AMZN	20130420	295	Call	0.97
AMZN	2013412	255.00	Call						AMZN	20130420	230	Put	0.01
AMZN	2013412	265.00	Call						AMZN	20130622	285	Call	-0.01
AMZN	2013412	265.00	Call						AMZN	20130622	290	Call	0.36
AMZN	2013420	235.00	Call						AMZN	20130420	155	Put	0.31
AMZN	2013420	235.00	Call						AMZN	20130420	225	Call	0
AMZN	2013420	240.00	Call
AMZN	2013420	240.00	Call
AMZN	2013420	245.00	Call

<colgroup><col style="width: 48pt;" span="14" width="64"> <tbody>
</tbody>

 

[max klein]

basically the two different sides are two different files/ sheets. i need the formula to math the criteria on the row on the left with the data on the right. when each criteria matches i need the delta value returned to the empty delta column. does that make sense?

Sorry for the lack of knowledge. thank you!

 

[drgs]

Should work, I guess
In column E in the right table:
=Index(MatchQuery('lefttable'!E:E;'lefttable'!A:A;'righttable'!A2;'lefttable'!B:B;'righttable'!B2; 'lefttable'!C:C;'righttable'!C2; 'lefttable'!D:D;'righttable'!D2); 1)

Public Function IsRange(vrn As Variant) As Boolean
    If IsObject(vrn) Then
        If Not vrn Is Nothing Then
            IsRange = TypeOf vrn Is Excel.Range
        End If
    End If
End Function


Public Function Union(ByRef rng1 As Range, _
                      ByRef rng2 As Range) As Range
    If rng1 Is Nothing Then
        Set Union = rng2
        Exit Function
    End If
    If rng2 Is Nothing Then
        Set Union = rng1
        Exit Function
    End If
    If Not rng1.Worksheet Is rng2.Worksheet Then
        Exit Function
    End If
    Set Union = Application.Union(rng1, rng2)
End Function

Public Function Intersect(ByRef rng1 As Range, _
                          ByRef rng2 As Range) As Range
    If rng1 Is Nothing Or rng2 Is Nothing Then
        Exit Function
    End If
    If Not rng1.Worksheet Is rng2.Worksheet Then
        Exit Function
    End If
    Set Intersect = Application.Intersect(rng1, rng2)
End Function



Public Function MatchAll(ByRef vrnLookupValue As Variant, _
                         ByRef rngLookupArray As Range) As Range
    Dim rngArea As Range
    Dim rngTemp1 As Range
    Dim rngTemp2 As Range
    Dim vrnMatch As Variant
    Dim lngCount As Long
    Dim lngLast As Long


    If rngLookupArray Is Nothing Then
        Exit Function
    End If
    For Each rngArea In rngLookupArray.Areas
        If rngArea.Columns.Count > rngArea.Rows.Count Then
            Set rngTemp1 = rngArea.Rows
        Else
            Set rngTemp1 = rngArea.Columns
        End If
        For Each rngTemp2 In rngTemp1
            With rngTemp2
                lngCount = .Cells.Count
                lngLast = 0
                Do
                    vrnMatch = Application.Match(vrnLookupValue, .Parent.Range(.Cells(lngLast + 1), .Cells(lngCount)), 0)
                    If IsError(vrnMatch) Then
                        Exit Do
                    End If
                    lngLast = lngLast + vrnMatch
                    Set MatchAll = Union(MatchAll, .Cells(lngLast))
                Loop Until lngLast = lngCount
            End With
        Next rngTemp2
    Next rngArea
End Function


Public Function MatchQuery(ParamArray vrnArgs() As Variant) As Range
    Dim rngResult As Range
    Dim i As Long
    Dim rngLookupArray As Range
    Dim rngMatches As Range
    
    If Not IsRange(vrnArgs(0)) Then
        Exit Function
    End If
    Set rngResult = vrnArgs(0)
    For i = 1 To UBound(vrnArgs) - 1 Step 2
        If Not IsRange(vrnArgs(i)) Then
            Exit Function
        End If
        Set rngLookupArray = vrnArgs(i)
        Set rngMatches = MatchAll(vrnArgs(i + 1), Intersect(rngResult.EntireRow, rngLookupArray))
        If rngMatches Is Nothing Then
            Exit Function
        End If
        Set rngResult = Application.Intersect(rngResult, rngMatches.EntireRow)
    Next i
    Set MatchQuery = rngResult
End Function