Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Special Vlookup?
Good evening....
In a worksheet W1, I have a situation where I an order number O1 that has been shipped in 1 to N shipments S1, S2,...Sn. In a worksheet W2, I have rows that represent the shipments but I only have the order number 01 to identify them. I would like to add a column and assign each row where O1 is the Shipment numbers S1 to Sn. On W1 A B __ __ 1 O1 S1 2 O1 S2 On W2 A B __ __ 1 O1 (Blank) 2 O1 (blank) Desired Result on W2 A B __ __ 1 O1 S1 2 O1 S2 If I use Vlookup I will only be able to match one for one, so If I put B1=+vlookup(A1,W2!A1:B2,2,False) I can get the value for the first row (in that case S1)... What would be the trick to guarantee that I would have S2 on W2!B2? Any help greatly appreciated Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Special Vlookup?
One way, if your data is on worksheet W1 A1:B7, on W2 enter in B1 and copy
down. After you type or paste the formula into the formula bar you must hit Control+Shift+Enter =INDEX(W1!$B$1:$B$7,SMALL(IF(W1!$A$1:$A$7=A1,ROW(I NDIRECT("1:"&ROWS(W1!$A$1:$A$7))),""),COUNTIF($A$1 :A1,A1))) "Michel Khennafi" wrote: Good evening.... In a worksheet W1, I have a situation where I an order number O1 that has been shipped in 1 to N shipments S1, S2,...Sn. In a worksheet W2, I have rows that represent the shipments but I only have the order number 01 to identify them. I would like to add a column and assign each row where O1 is the Shipment numbers S1 to Sn. On W1 A B __ __ 1 O1 S1 2 O1 S2 On W2 A B __ __ 1 O1 (Blank) 2 O1 (blank) Desired Result on W2 A B __ __ 1 O1 S1 2 O1 S2 If I use Vlookup I will only be able to match one for one, so If I put B1=+vlookup(A1,W2!A1:B2,2,False) I can get the value for the first row (in that case S1)... What would be the trick to guarantee that I would have S2 on W2!B2? Any help greatly appreciated Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP merge documents, copy-paste special... | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
VLOOKUP | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |