| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple VLookups - Can anyone help me please? | Excel Discussion (Misc queries) | |||
| Sum a row of multiple vlookups | Excel Worksheet Functions | |||
| Multiple Vlookups | Excel Worksheet Functions | |||
| multiple vlookups | Excel Worksheet Functions | |||
| multiple vlookups | Excel Worksheet Functions | |||
Linear Mode
