Looking up multiple rows from vlookups??
 

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

The simplest solution is to use AutoFilter. You can then display only rows
that have an "a" in column A and copy the data elsewhere.
--
Gary''s Student - gsnu200790


"confused" wrote:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

Hi,

Try this array entered (Ctrl+Shift+Enter)

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$C$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$C$1,ROW($A$1:$A$20)-ROW($C$1)+1),ROWS(B$1:B1))),"")

Search value is in C1 and it works on columns A and B. Drag down to find
second and subsequent matches.

Mike

"confused" wrote:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

Why don't you Autofilter column A for product a? Doing this you'll see on
ypou screen merely
Product Component
a xx
a xw
a ww

A more complicated question if you want to see in the same run components of
product xx as well!

Regards,
Stefi


€žconfused€� ezt Ã*rta:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

One simple play to take it out in adjacent cols ..

Assuming source data in cols A & B, data from row2 down
Assume the input for the desired product will be made in E1, eg: a

In D2: =IF(A2="","",IF(A2=E$1,ROW(),""))
Leave D2 blank

In E2: =IF(ROWS($1:1)COUNT(D:D),"",INDEX(B:B,SMALL(D:D,R OWS($1:1))))
Select D2:E2, copy down to cover the max expected extent of source data, say
down to E200? Minimize or hide away col D. Col E will return all the multiple
results sought for the product entered in E1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"confused" wrote:
Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

Thanks for that, unfortunately autofilter would then only give me "a" and I
would need "xx" , "11" and "12" as well. There is also about 1600 records in
the sheet so autofilter is a bit unpractical!



"Gary''s Student" wrote:

The simplest solution is to use AutoFilter. You can then display only rows
that have an "a" in column A and copy the data elsewhere.
--
Gary''s Student - gsnu200790


"confused" wrote:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

Hi Mike,

thanks for this, I managed to get this working on a small simple
spreadsheet, but not in the one I am working on.
The value I am looking up is in E4. The Column this value is in is C39:C1647
and the values I want to look up are in K39:K1647.

This is the formula I have typed in...

=IF(ROWS(C$39:C39)<=COUNTIF($C$39:$C$1647,$E$4),IN DEX($K$39:$K$1647,SMALL(IF($C$39:$C$1647=$E$4,ROW( $C$39:$C$1647)-ROW($E$4)+1),ROWS(C$39:C39))),"")


It is picking up results, but it seems to be random values from the list?
Any thoughts on this??

thank you


"Mike H" wrote:

Hi,

Try this array entered (Ctrl+Shift+Enter)

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$C$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$C$1,ROW($A$1:$A$20)-ROW($C$1)+1),ROWS(B$1:B1))),"")

Search value is in C1 and it works on columns A and B. Drag down to find
second and subsequent matches.

Mike

"confused" wrote:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

Hi,

I shortened the range to rows 39 - 52 for debubbing but this now works on E4
and columns C & K simply extend the ranges to what you want

=IF(ROWS(B$1:B1)<=COUNTIF($C$39:$C$52,$E$4),INDEX( $K$39:$K$52,SMALL(IF($C$39:$C$52=$E$4,ROW($C$39:$C $52)-ROW($C$1)-38),ROWS(B$1:B1))),"")

Mike

"confused" wrote:

Hi Mike,

thanks for this, I managed to get this working on a small simple
spreadsheet, but not in the one I am working on.
The value I am looking up is in E4. The Column this value is in is C39:C1647
and the values I want to look up are in K39:K1647.

This is the formula I have typed in...

=IF(ROWS(C$39:C39)<=COUNTIF($C$39:$C$1647,$E$4),IN DEX($K$39:$K$1647,SMALL(IF($C$39:$C$1647=$E$4,ROW( $C$39:$C$1647)-ROW($E$4)+1),ROWS(C$39:C39))),"")


It is picking up results, but it seems to be random values from the list?
Any thoughts on this??

thank you


"Mike H" wrote:

Hi,

Try this array entered (Ctrl+Shift+Enter)

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$C$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$C$1,ROW($A$1:$A$20)-ROW($C$1)+1),ROWS(B$1:B1))),"")

Search value is in C1 and it works on columns A and B. Drag down to find
second and subsequent matches.

Mike

"confused" wrote:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

On reflection use this instead, same comment regarding the ranges

=IF(ROWS(B$39:B39)<=COUNTIF($C$39:$C$52,$E$4),INDE X($K$39:$K$52,SMALL(IF($C$39:$C$52=$E$4,ROW($C$39: $C$52)-ROW($C$39)+1),ROWS(B$39:B39))),"")

Mike

"confused" wrote:

Hi Mike,

thanks for this, I managed to get this working on a small simple
spreadsheet, but not in the one I am working on.
The value I am looking up is in E4. The Column this value is in is C39:C1647
and the values I want to look up are in K39:K1647.

This is the formula I have typed in...

=IF(ROWS(C$39:C39)<=COUNTIF($C$39:$C$1647,$E$4),IN DEX($K$39:$K$1647,SMALL(IF($C$39:$C$1647=$E$4,ROW( $C$39:$C$1647)-ROW($E$4)+1),ROWS(C$39:C39))),"")


It is picking up results, but it seems to be random values from the list?
Any thoughts on this??

thank you


"Mike H" wrote:

Hi,

Try this array entered (Ctrl+Shift+Enter)

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$C$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$C$1,ROW($A$1:$A$20)-ROW($C$1)+1),ROWS(B$1:B1))),"")

Search value is in C1 and it works on columns A and B. Drag down to find
second and subsequent matches.

Mike

"confused" wrote:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie

Looking up multiple rows from vlookups??
 

Brilliant Mike
!
i've no idea how it works, but it does!

Thank you

"Mike H" wrote:

On reflection use this instead, same comment regarding the ranges

=IF(ROWS(B$39:B39)<=COUNTIF($C$39:$C$52,$E$4),INDE X($K$39:$K$52,SMALL(IF($C$39:$C$52=$E$4,ROW($C$39: $C$52)-ROW($C$39)+1),ROWS(B$39:B39))),"")

Mike

"confused" wrote:

Hi Mike,

thanks for this, I managed to get this working on a small simple
spreadsheet, but not in the one I am working on.
The value I am looking up is in E4. The Column this value is in is C39:C1647
and the values I want to look up are in K39:K1647.

This is the formula I have typed in...

=IF(ROWS(C$39:C39)<=COUNTIF($C$39:$C$1647,$E$4),IN DEX($K$39:$K$1647,SMALL(IF($C$39:$C$1647=$E$4,ROW( $C$39:$C$1647)-ROW($E$4)+1),ROWS(C$39:C39))),"")


It is picking up results, but it seems to be random values from the list?
Any thoughts on this??

thank you


"Mike H" wrote:

Hi,

Try this array entered (Ctrl+Shift+Enter)

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$C$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$C$1,ROW($A$1:$A$20)-ROW($C$1)+1),ROWS(B$1:B1))),"")

Search value is in C1 and it works on columns A and B. Drag down to find
second and subsequent matches.

Mike

"confused" wrote:

Hello,

Is there any was to use vlookup to retrieve more than the first entry. My
spreadsheet is as follows...

Product Component
a xx
a xw
a ww
xx 11
xx 12
12 abc
12 def

So each product is made up of numerous components, and these components
themselves are made up of sub components.
If I vlookup product A I can retireve component xx, but not xw or ww . Is
there any way of doing this??

Many thanks,

Willie