|
VLookup - multiple values
Hello
I have a spreadsheet with two columns - CLUSTER and ID NUMBER Each cluster may have four or five product IDs associated with it... CLUSTER PROD_ID GL01 6867 GL01 6931 GL01 6961 GL01 6962 GL01 6966 GL01 6967 GL02 6012 GL02 6015 GL02 6025 GL03 5063 GL03 5064 GL03 5099 GL03 5123 GL04 3092 GL04 5059 GL04 5068 GL04 5081 GL04 5082 GL04 5150 GL04 5301 GL04 5358 GL04 5372 GL04 5420 GL04 5421 Is there are way to present this information, so that the column A has the cluster number and the following columns have the product IDs for that cluster: CLUSTER PROD_1 PROD_2 PROD_3 PROD_4 PROD_5 PROD_6 GL01 6867 6931 6961 6962 6966 6967 GL02 6012 6015 6025 GL03 5063 5064 5099 5123 Thanks Ben |
VLookup - multiple values
Hi Ben
Assuming Cluster in A1:A25 and Prod_Id in B1:B25, and the unique clusters listed in C2:C5 and you want to fill the prods in D2:I5 then you can use the following formula: =INDEX($B$2:$B$25,LARGE(($A$2:$A$25=$C2)*(ROW($A$2 :$A$25))-ROW($A $2)+1,COLUMNS($D$1:D$1))) which must be array entered (Ctrl+Shift+Enter) as it is an array formula. So that you don't get error values (eg for clusters without Prod_6 for example then: =IF(ISERROR(INDEX($B$2:$B$25,LARGE(($A$2:$A$25=$C2 )*(ROW($A$2:$A$25))- ROW($A$2)+1,COLUMNS($D$1:D$1)))),"",INDEX($B$2:$B$ 25,LARGE(($A$2:$A$25= $C2)*(ROW($A$2:$A$25))-ROW($A$2)+1,COLUMNS($D$1:D$1)))) Hope this helps! Richard On 29 Jan, 10:19, Ben wrote: Hello I have a spreadsheet with two columns - CLUSTER and ID NUMBER Each cluster may have four or five product IDs associated with it... CLUSTER PROD_ID GL01 6867 GL01 6931 GL01 6961 GL01 6962 GL01 6966 GL01 6967 GL02 6012 GL02 6015 GL02 6025 GL03 5063 GL03 5064 GL03 5099 GL03 5123 GL04 3092 GL04 5059 GL04 5068 GL04 5081 GL04 5082 GL04 5150 GL04 5301 GL04 5358 GL04 5372 GL04 5420 GL04 5421 Is there are way to present this information, so that the column A has the cluster number and the following columns have the product IDs for that cluster: CLUSTER PROD_1 PROD_2 PROD_3 PROD_4 PROD_5 PROD_6 GL01 6867 6931 6961 6962 6966 6967 GL02 6012 6015 6025 GL03 5063 5064 5099 5123 Thanks Ben |
VLookup - multiple values
That works a treat - thanks!!!
Ben "RichardSchollar" wrote: Hi Ben Assuming Cluster in A1:A25 and Prod_Id in B1:B25, and the unique clusters listed in C2:C5 and you want to fill the prods in D2:I5 then you can use the following formula: =INDEX($B$2:$B$25,LARGE(($A$2:$A$25=$C2)*(ROW($A$2 :$A$25))-ROW($A $2)+1,COLUMNS($D$1:D$1))) which must be array entered (Ctrl+Shift+Enter) as it is an array formula. So that you don't get error values (eg for clusters without Prod_6 for example then: =IF(ISERROR(INDEX($B$2:$B$25,LARGE(($A$2:$A$25=$C2 )*(ROW($A$2:$A$25))- ROW($A$2)+1,COLUMNS($D$1:D$1)))),"",INDEX($B$2:$B$ 25,LARGE(($A$2:$A$25= $C2)*(ROW($A$2:$A$25))-ROW($A$2)+1,COLUMNS($D$1:D$1)))) Hope this helps! Richard On 29 Jan, 10:19, Ben wrote: Hello I have a spreadsheet with two columns - CLUSTER and ID NUMBER Each cluster may have four or five product IDs associated with it... CLUSTER PROD_ID GL01 6867 GL01 6931 GL01 6961 GL01 6962 GL01 6966 GL01 6967 GL02 6012 GL02 6015 GL02 6025 GL03 5063 GL03 5064 GL03 5099 GL03 5123 GL04 3092 GL04 5059 GL04 5068 GL04 5081 GL04 5082 GL04 5150 GL04 5301 GL04 5358 GL04 5372 GL04 5420 GL04 5421 Is there are way to present this information, so that the column A has the cluster number and the following columns have the product IDs for that cluster: CLUSTER PROD_1 PROD_2 PROD_3 PROD_4 PROD_5 PROD_6 GL01 6867 6931 6961 6962 6966 6967 GL02 6012 6015 6025 GL03 5063 5064 5099 5123 Thanks Ben |
| All times are GMT +1. The time now is 10:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com