Compare two ranges of cells and return a value bas

Question

Can anyone point me at a function or code that can compare a range of cells to a range of cells in another sheet and return a value. The lists have the same format:

Example:

Compare Sheet1 A1 B1 C1 D1 to Sheet 2 A1 B1 C1 D1 and return Sheet 2 E1 value

In my head it's like a multiple column VLOOKUP.

Answer 1

If you can move the job code to E2 then this, adapted from the link siddharth posted will work:

 Dim a As Application
 Set a = Application

    If Join(a.Transpose(a.Transpose(Sheets(1).Rows(1).Value)), chr(0)) = _
   Join(a.Transpose(a.Transpose(Sheets(2).Rows(1).Value)), chr(0)) Then


MsgBox (Sheets(2).Cells(2, "E"))
End If

Change the rows as necessary, and instead of msgbox, save it as a variable or however you want.

Edit

the following will work also:

Dim a As Application
Set a = Application
Dim rngA As Range
Dim rngB As Range

Set rngA = Range("A1", "D1")
Set rngB = Range("A2", "D2")


If Join(a.Transpose(a.Transpose(rngA.Value)), chr(0)) = _
   Join(a.Transpose(a.Transpose(rngB.Value)), chr(0)) Then


    MsgBox (Sheets(2).Cells(2, "E"))
End If

provided that the ranges to compare are only 1 row each. if either range spans more than one row it wont work.

Edit2

Sub getValuesOnRow()
  Dim sourceRange As Range
  Dim targetRange As Range

  Set sourceRange = ActiveSheet.Range(Cells(1, 1), Cells(5, 1))
  Set targetRange = ActiveSheet.Cells(7, 1)

  sourceRange.Copy
  targetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,     Transpose:=True

End Sub

Sub Compare()
Dim a As Application
Set a = Application
Dim rngA As Range
Dim rngB As Range


Set rngA = Sheets(1).Range("A1", "A6")
Set rngB = Sheets(2).Range("A1", "A6")


If Join(a.Transpose(a.Transpose(Sheets(1).Rows(7).Value)), chr(0)) = _
   Join(a.Transpose(a.Transpose(Sheets(2).Rows(7).Value)), chr(0)) Then


    Sheets(1).Cells(6, "A").Value = Sheets(2).Cells(6, "A")
End If



End Sub

So that there is 2 methods. the first, takes a particular column, in the example, column 1, and the first 5 cells, and puts it on 1 row. in the example, row 7. Then the transpose method, takes the row and compares it. so the first method can be used to get a particular column on a row, and the second can be used to compare 2 rows and return a particular cell if the comparison is true.