Returning value when multiple criteria are met.

M

max klein

New Member
Joined
Jul 15, 2013
Messages
4
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
max klein said:
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
Click to expand...

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
 
Upvote 0
[TABLE="width: 672"]
<colgroup><col style="width: 48pt;" span="14" width="64"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: #00B0F0"]Underlying[/TD]
[TD="class: xl64, width: 64, bgcolor: #00B0F0"]Expiration Date[/TD]
[TD="class: xl65, width: 64, bgcolor: #00B0F0"]Strike[/TD]
[TD="class: xl63, width: 64, bgcolor: #00B0F0"]Call/Put[/TD]
[TD="class: xl66, width: 64, bgcolor: #00B0F0"]Series Delta
(End of Day)[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: #00B0F0"]Underlying[/TD]
[TD="class: xl64, width: 64, bgcolor: #00B0F0"]Expiration Date[/TD]
[TD="class: xl65, width: 64, bgcolor: #00B0F0"]Strike[/TD]
[TD="class: xl63, width: 64, bgcolor: #00B0F0"]Call/Put[/TD]
[TD="class: xl66, width: 64, bgcolor: #00B0F0"]Series Delta
(End of Day)[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013412[/TD]
[TD="class: xl69, bgcolor: transparent"]250.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AMZN[/TD]
[TD="class: xl70, bgcolor: transparent"]20130412[/TD]
[TD="bgcolor: transparent, align: right"]255[/TD]
[TD="bgcolor: transparent"]Put[/TD]
[TD="bgcolor: transparent, align: right"]-0.24[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013412[/TD]
[TD="class: xl69, bgcolor: transparent"]250.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AMZN[/TD]
[TD="class: xl70, bgcolor: transparent"]20130412[/TD]
[TD="bgcolor: transparent, align: right"]260[/TD]
[TD="bgcolor: transparent"]Call[/TD]
[TD="bgcolor: transparent, align: right"]-0.01[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013412[/TD]
[TD="class: xl69, bgcolor: transparent"]255.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AMZN[/TD]
[TD="class: xl70, bgcolor: transparent"]20130420[/TD]
[TD="bgcolor: transparent, align: right"]295[/TD]
[TD="bgcolor: transparent"]Call[/TD]
[TD="bgcolor: transparent, align: right"]0.97[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013412[/TD]
[TD="class: xl69, bgcolor: transparent"]255.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AMZN[/TD]
[TD="class: xl70, bgcolor: transparent"]20130420[/TD]
[TD="bgcolor: transparent, align: right"]230[/TD]
[TD="bgcolor: transparent"]Put[/TD]
[TD="bgcolor: transparent, align: right"]0.01[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013412[/TD]
[TD="class: xl69, bgcolor: transparent"]265.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AMZN[/TD]
[TD="class: xl70, bgcolor: transparent"]20130622[/TD]
[TD="bgcolor: transparent, align: right"]285[/TD]
[TD="bgcolor: transparent"]Call[/TD]
[TD="bgcolor: transparent, align: right"]-0.01[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013412[/TD]
[TD="class: xl69, bgcolor: transparent"]265.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AMZN[/TD]
[TD="class: xl70, bgcolor: transparent"]20130622[/TD]
[TD="bgcolor: transparent, align: right"]290[/TD]
[TD="bgcolor: transparent"]Call[/TD]
[TD="bgcolor: transparent, align: right"]0.36[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013420[/TD]
[TD="class: xl69, bgcolor: transparent"]235.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AMZN[/TD]
[TD="class: xl70, bgcolor: transparent"]20130420[/TD]
[TD="bgcolor: transparent, align: right"]155[/TD]
[TD="bgcolor: transparent"]Put[/TD]
[TD="bgcolor: transparent, align: right"]0.31[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013420[/TD]
[TD="class: xl69, bgcolor: transparent"]235.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AMZN[/TD]
[TD="class: xl70, bgcolor: transparent"]20130420[/TD]
[TD="bgcolor: transparent, align: right"]225[/TD]
[TD="bgcolor: transparent"]Call[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013420[/TD]
[TD="class: xl69, bgcolor: transparent"]240.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013420[/TD]
[TD="class: xl69, bgcolor: transparent"]240.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]AMZN[/TD]
[TD="class: xl68, bgcolor: transparent"]2013420[/TD]
[TD="class: xl69, bgcolor: transparent"]245.00[/TD]
[TD="class: xl67, bgcolor: transparent"]Call[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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!
 
Upvote 0
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)


Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
IF statement to return different words for condition A, condition B, or both A and B.
Replies
6
Views
172
Fluff
Fluff
O
  • Question Question
Count only one occurrence of all criteria being met
Replies
8
Views
1K
KRice
K
R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
68
Rokcmd
R
J
  • Solved
Data Validation with Multiple Conditions
Replies
4
Views
614
dreid1011
dreid1011
T
  • Solved
=IF Formula Question
Replies
5
Views
481
Fluff
Fluff

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top