You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JJ" wrote in message
...
Tx a mil guys, I appreciate your assistance.

Both formulas worked.

JJ

"T. Valko" wrote:

Try this...

In the formulas:

Rng1 refers to worksheet B $A$2:$A$5
Rng2 refers to worksheet B $B$2:$B$5
ReturnThis refers to worksheet B $C:$C

On worksheet A enter this formula in A2. This will return the count of
records that meet the conditions.

=SUMPRODUCT(--(Rng1=A1),--(LEFT(Rng2,2)="PL"))

On worksheet A enter this array formula** in B1:

=IF(ROWS(B$1:B1)A$2,"",INDEX(ReturnThis,SMALL(IF( Rng1=A$1,IF(LEFT(Rng2,2)="PL",ROW(Rng1))),ROWS(B$1 :B1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy the formula in B1 down until you get blanks.

--
Biff
Microsoft 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.


.