Looking up multiple rows from vlookups??

[confused]

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

 

[Gary''s Student]

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.

 

[Mike H]

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

 

[Stefi]

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:

 

[Max]

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),"",INDEX(B:B,SMALL(D,ROWS($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.

 

[confused]

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!

 

[confused]

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),INDEX($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]

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

 

[Mike H]

On reflection use this instead, same comment regarding the ranges

=IF(ROWS(B$39:B39)<=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$39)+1),ROWS(B$39:B39))),"")

Mike

 

[confused]

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

Thank you