Lookup Values, return multiple.

[JJ]

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] [C]
SP001 PL001 16
SP001 PL002 5
SP001 DR001 10
CR001 PL001 2

Search for all Text String starting with "PL" in Worksheet 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.

 

[T. Valko]

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.

 

[Bernie Deitrick]

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,'Worksheet
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,'Worksheet 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]

Tx a mil guys, I appreciate your assistance.

Both formulas worked.

JJ

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

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] [C]
SP001 PL001 16
SP001 PL002 5
SP001 DR001 10
CR001 PL001 2

Search for all Text String starting with "PL" in Worksheet 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.

.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Tx a mil guys, I appreciate your assistance.

Both formulas worked.

JJ

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

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] [C]
SP001 PL001 16
SP001 PL002 5
SP001 DR001 10
CR001 PL001 2

Search for all Text String starting with "PL" in Worksheet 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.

.