Excel 2007 – Using combination of Countif and Indirect

I don't think there is any easy way to do this with existing Excel formulas. The problem is two-fold. First, AFAIK there is no Excel function that will tell you if a cell is part of a merged range. Second, the value shown in a merged ragne is actually only in the first cell of the merged range.

If you are willing to use VBA to create a custom function, this can be done fairly easily with a combination of the Match function and the fact that Range objects know if they are part of a merged range.

Public Function VLookupMerge(lookup_value As Variant, table_array As Range, col_index As Long) As Variant
   Dim sMatchFormula As String
   Dim row_index As Variant
   Dim r As Range

   sMatchFormula = "=MATCH(" & lookup_value _
                        & "," & table_array.Columns(1).Address(External:=True) _
                        & ",0)"
   row_index = Application.Evaluate(sMatchFormula)

   If TypeName(row_index) = "Error" Then
      VLookupMerge = row_index
   Else
      Set r = table_array.Cells(row_index, col_index)
      VLookupMerge = r.MergeArea.Range("A1")
      Set r = Nothing
   End If

End Function

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