Lookup Values, return multiple.

J

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

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.
 
B

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
 
J

JJ

Tx a mil guys, I appreciate your assistance.

Both formulas worked.

JJ

T. Valko said:
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 said:
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

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


JJ said:
Tx a mil guys, I appreciate your assistance.

Both formulas worked.

JJ

T. Valko said:
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 said:
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.



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top