Lookup whole rows from one sheet in another sheet?

[chive90]

For example, in Sheet1, I need to see if A1 all the way through to Z1 matches A* to Z* in Sheet2. Sheet2 has the exact same columns. In Sheet2 obviously the match could be any row and won't be the 1st row like it is in Sheet1. But if A1-Z1 in Sheet1 for example is found at A500-Z500 in Sheet2, I would get a match. If it is not found anywhere in Sheet2, I would get an #N/A.

I assume another option would be to just Concatenate all of the columns into a new column, and look up that column in the other sheet... but is there a better way to lookup a whole row from one sheet into another?

Thanks

 

[offthelip]

you can do it very rapidly using the dictionary object in VBA to concatenate the rows together, try this:

Sub dicindexmatch()
' This uses Multiple columns in a match between sheet 2 and sheet 1 for equivalent of index and match
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
  lastcol = 26   ' define the last column
 
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Sheet2")
      LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(LastRow, lastcol))
   End With
   For i = 2 To UBound(Ary)
    ' concatentate colums A to Z
      temp = Ary(i, 1)
      For j = 2 To lastcol
      temp = temp & Ary(i, j)
      Next j
      Dic(temp) = i                 ' save array row in dictionary
   Next i
   With Worksheets("Sheet1")
      LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    
      inarr = .Range(.Cells(1, 1), .Cells(LastRow, lastcol))
      outarr = .Range(.Cells(1, lastcol + 1), .Cells(LastRow, Lastcol + 1))
 
      For i = 2 To LastRow
         temp = inarr(i, 1)
         For j = 2 To lastcol
         temp = temp & inarr(i, j)  '     ' concatentate colums A to Z
         Next j
         If Dic.Exists(temp) Then   ' check if element exists
         outarr(i, 1) = Dic(temp) 'this matches the value given by the index in the dictionary
         Else          ' if it doesn't set to not found
         outarr(i, 1) = "Not Found"
         End If
      
      Next i
      outarr(1, 1) = " Results: row number on sheet2"
      .Range(.Cells(1, lastcol + 1), .Cells(LastRow, lastcol + 1)) = outarr ' write out the output array
  
   End With
End Sub