Match Two Columns with Two Other Columns

Excel: match two columns with two other columns

I'll give two answers.

The first requires the matches to be in the same rows for column C and D. So if A2 matches C3,C4,C5 then B2 will need to match D3,D4 or D5

=IF(SUMPRODUCT(--($C$1:$C$5=A1),--($D$1:$D$5=B1))>0,1,0)

From the inside out
--($C$1:$C$5=A1) compares A1 to all the values in C1 to C5 and returns a 1 if true and a 0 if false
Sumproduct multiplies these together so you need to get trues in both sides
If statement is just used to limit the number to 1 (it would return 2 if 2 rows matched etc)

To expand based on questions
-Yes you could use --(C:C=A1) inside the formula it just takes much longer to compute since it is working on many more cells

--(C:C=A1) will return an array that looks like this {0,0,0,1,0,0,1} with a 1 everytime a cell in column C is equal to A1.

--(D:D=B1) formula will do the same with 1s everytime a cell in D matches B1 say {0,1,0,0,0,0,1}.
Sumproduct multiplies those arrays {0*0,1*0,0*0,1*0,0*0,0*0,1*1} and adds up the results (0+0+0+0+0+0+1) = 1.

This sumproduct formula can return any interger value (0 if there is no match, 1 if there is one set of matched cells, 2 if there are 2 sets of matched cells, etc).

By wrapping it in an if(sumproduct(..)>0,1,0) just means it will always return a 0 (no match) or 1 (one or more matches).

Hope this helps.

Edit: Formula not needed

Next formula looks to match A2 somewhere in column C, and the then looks to match B2 somewhere in column D doesn't have to be the same row.

=IF(IFERROR(MATCH(A1,$C$1:$C$5,0)*MATCH(B1,$D$1:$D$5,0),0)>0,1,0)

match you know
iferror just makes it return 0 if nothing matches
If statement just returns a 1 if the value is anything >0 (if both columns have a match).

Match two Columns and Pick corresponding value with DateTime validation

With Excel 2019+ or Office O365

=MINIFS($G$2:$G$100,$H$2:$H$100,B2,$G$2:$G$100,">" & A2)

Edit: Missed a set of parentheses
If you have an earlier version of Excel, try:

=AGGREGATE(15,6,1/(($H$2:$H$100=B2)*($G$2:$G$100>A2))* $G$2:$G$100,1)

Both formulas will return #NUM! error if no match found.

match two columns with two other columns

You have not indicated what you would consider a correct answer and your terminology seems a bit vague when you talk about "where there is a reciprocal match", but if I understand the task correctly as finding all rows where col.3 == col.10 & col.4 == col.11, then this should accomplish the task:

which( outer(indat$V4, indat$V11, "==") & 
       outer(indat$V3, indat$V10, "=="), 
       arr.ind=TRUE)
# result
      row col
 [1,]  19   1
 [2,]  10   3
 [3,]   7   6
 [4,]   8   6
 [5,]   6   7
 [6,]  11   8
 [7,]   3  10
 [8,]   7  11
 [9,]   8  11
[10,]   1  19

The outer function applies a function 'FUN', in this case "==", to all two-way combinations of x and y, its first and second arguments, so here we get an n x n matrix with logical entries and I am taking the logical 'and' of two such matrices. So the rows where there are matches with other rows are:

unique( c(which( outer(indat$V4, indat$V11, "==") & 
outer(indat$V3, indat$V10, "=="), 
arr.ind=TRUE) ))

#[1] 19 10  7  8  6 11  3  1

So the set with no matches, assuming a data.frame named indat, is:

matches <- unique( c(which( outer(indat$V4, indat$V11, "==") & 
                      outer(indat$V3, indat$V10, "=="), arr.ind=TRUE) ))
indat[ ! 1:NROW(indat) %in% matches, ]

And the ones with matches are:

indat[ 1:NROW(indat) %in% matches, ]

Match 2 Columns and Result from 3rd Column

Match Columns Using a Dictionary of Dictionaries

Sub MatchColumns()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet2")
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion ' ("A1:D13")
    Dim rCount As Long: rCount = srg.Rows.Count - 1
    Dim Data As Variant: Data = srg.Resize(rCount).Offset(1).Value ' ("A2:D13")
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim Key As Variant
    Dim r As Long

    For r = 1 To rCount
        Key = Data(r, 1)
        If Not dict.Exists(Key) Then
            Set dict(Key) = CreateObject("Scripting.Dictionary")
        End If
        dict(Key)(Data(r, 3)) = Data(r, 4)
    Next r
    
    ' Print the contents of the dictionary in the Immediate window (Ctrl+G).
'    Dim iKey As Variant
'    For Each Key In dict.Keys
'        Debug.Print Key
'        For Each iKey In dict(Key).Keys
'            Debug.Print iKey
'        Next iKey
'    Next Key
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1")
    
    Dim drrg As Range ' The Row (Column Labels, Headers) ' ("B1:E1")
    Set drrg = dws.Range("B1", dws.Cells(1, dws.Columns.Count).End(xlToLeft))
    Dim rData As Variant: rData = drrg.Value
    Dim cCount As Long: cCount = drrg.Columns.Count
    
    Dim dcrg As Range ' The Column (Row Labels) ' ("A3:A5")
    Set dcrg = dws.Range("A3", dws.Cells(dws.Rows.Count, "A").End(xlUp))
    Dim cData As Variant: cData = dcrg.Value
    rCount = dcrg.Rows.Count
    
    ReDim Data(1 To rCount, 1 To cCount)
    
    Dim c As Long
    
    For r = 1 To rCount
        Key = cData(r, 1)
        If dict.Exists(Key) Then
            For c = 1 To cCount
                If dict(Key).Exists(rData(1, c)) Then
                    Data(r, c) = dict(Key)(rData(1, c))
                End If
            Next c
        End If
    Next r
    
    dws.Range("B3").Resize(rCount, cCount).Value = Data ' ("B3:E5")
    
End Sub

Match two columns and return values based on a condition in two other columns

Use VLOOKUP and compare the results:

=IF(VLOOKUP(G2,A:B,2,FALSE)=VLOOKUP(G2,C:D,2,FALSE),"MATCH",VLOOKUP(G2,C:D,2,FALSE))

Compare Two columns If 2 cells are Matching

I think your question is hard to follow but I'll give it a shot.
If you only want to compare values with in the single row use and not look at a different row use:

=IF(AND(B3=D3,C3=E3),"","d")

If want to know if the values in one row of E&F match exactly the rows of columns B&C use COUNTIFS().

=IF(COUNTIFS(B:B,D3,C:C,E3)>0,"","d")

Subset data frame if two columns combined match two other columns exactly

What you're looking for is a left join:

> library(dplyr)
> left_join(x1, x2, by = c('state', 'county'))
  state   county UR
1    FL    Duval  4
2    FL Columbia  5
3    TX   Dallam  4
4    TX   Dimmit  6

Or using merge from base R:

> merge(x1, x2, all.x = T)
  state   county UR
1    FL Columbia  5
2    FL    Duval  4
3    TX   Dallam  4
4    TX   Dimmit  6

---

Related Topics