Lookup Values, return multiple.

  • Thread starter Thread starter JJ
  • Start date Start date
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.
 
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.
 
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
 
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.
Click to expand...



.
Click to expand...
 
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.
Click to expand...



.
Click to expand...
Click to expand...
 
Back
Top