JJ,

If PL isn't restricted to being the leading two letters in column B, then you need to use an array
formula like this

=IF(SUMPRODUCT(('Worksheet A'!$A$1:$A$5000=$A$1)*(ISNUMBER(FIND($B$1,'Workshe et
A'!$B$1:$B$5000))))=ROW(A1),INDEX('Worksheet A'!$C:$C,SMALL(IF(('Worksheet
A'!$A$1:$A$5000=$A$1)*(ISNUMBER(FIND($B$1,'Workshe et A'!$B$1:$B$5000))),ROW('Worksheet
A'!$A$1:$A$5000),10000),ROW(A1))),"")

Where cell A1 contains SP001, and B1 contains PL.

Enter using Ctrl-Shift-Enter, then copy down until you get blanks.

HTH,
Bernie
MS Excel MVP


"JJ" wrote in message
...
I need help please.

The formula I require has to in Worksheet A :-
[B1]

lookup DATA in Worksheet A
[A1]
SP001

in worksheet B
[A] [b] [C]
SP001 PL001 16
SP001 PL002 5
SP001 DR001 10
CR001 PL001 2

Search for all Text String starting with "PL" in Worksheet B [b] only for
SP001 in [A]

and return with values from Worksheet B [C]
16 and 5

I need the values to be seperated and not summed.

Tx. Appreciate assistance.