return multiple cell values based on multiple criteria

[MMM_84]

Looking for a vba that returns different cell values based on multiple matching criteria. I was using below, but that one works only with 2 cells matching value.

Test Worksheet is the main one, that contains table with headers (A1:F50)
Result Worksheet has to return cell values from "test" wsh columns A, C and D onto the table A2:G50 based on

"reslust" wsh range A2:A50 value to be equal to "test" wsh range E2:E10
"reslust" wsh range A1:G1 value to be equal to "test" wsh range B2:B50


Option Explicit
Sub FindValues()
Dim lookUpSheet As Worksheet, updateSheet As Worksheet
Dim i As Long, lastRow as Long
Dim sResult As String, sKey As String
Set lookUpSheet = Worksheets("Test")
Set updateSheet = Worksheets("Result")
sKey = updateSheet.Range("A2").Value & updateSheet.Range("E1").Value ' should be the range A2:A50 and range A1:G1, that matches below range '
with lookUpSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 2 To lastRow
If .Cells(i, 5).Value & .Cells(i, 2).Value = sKey Then
sResult = sResult & vbNewLine & .Cells(i, 1).Value & vbNewLine _
& .Cells(i, 3).Value & vbNewLine & .Cells(i, 4).Value
End If
Next i
end with
updateSheet.Cells(2, 2).Value = mid(sResult,2) ' the range A2:G50 should be updated, instead of one single cell '
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).