Excel – Compare cells in two different spreadsheets and extract data from one an place it in the other if match found

YMMV, but View -> Macros, add a macro. Try this (change the cell references as necessary):

Dim data, reference As Range

Set reference = Worksheets("Sheet2").Range("A1", "A42")
Set data = Worksheets("Sheet1").Range("B2", "F6")

For Each dataCell In data
    For Each referenceCell In reference
        If dataCell.Value = referenceCell.Value Then
                Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
        End If
    Next
Next

[Edit: If this works, you can speed it up a bit by stopping searching a row when you've found a match. (Assuming only one possible match per row). e.g.:

Sub newtest()
    Dim data, reference As Range
    Dim skipsome As Boolean
    skipsome = False

    Set reference = Worksheets("Sheet2").Range("A1", "A7")

    Set data = Worksheets("Sheet1").Range("B2", "F6")
    For Each dataCell In data
        For Each referenceCell In reference
            If dataCell.Value = referenceCell.Value Then
                    Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
                    skipsome = True
                    Exit For
            End If

            If skipsome = True Then
                skipsome = False
                Exit For
            End If
        Next
    Next
End Sub

Just on your 5 row test data this knocks the cell comparison tests down from 175 to 132.] [Edit2: Make the code work]

Based on what I have understood...You can explore VLOOKUP function. I am assuming that "X" is content of column somewhere next to the column B in P&S sheet.

Sample formula

=IF(ISERROR(VLOOKUP(B1,'P&S'!$B$1:$C$6,2,FALSE)),"",VLOOKUP(B1,'P&S'!$B$1:$C$6,2,FALSE))

B1 - Contents of cell B1 (in SLS sheet) VLOOKUP B1 against range of B1 thru B6 (in this case) in sheet P&S and if match is found pull contents of 2nd column (your X) in formula cell.

IF is required to remove #N/A (not found) and replace with "" (Blank)

Don't forget prefix $ to prevent range being moved down as you copy formula