Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning multiple indices for a lookup
Hi,
I have a table which contains a name in the first column and an article-number in the second column. One person can have multiple articles but the article numbers are unique. I am now trying to generate a table showing which person has which articles, sorted per name whereby the name is given for each line (is already sorted as needed). In the output I also have a 'counter' going across for easier readability. I'll try and give an example: Table1 (exists): Name Article John 1234 John 2345 James 3456 Jim 4567 John 5678 James 6789 Table2 (needed): 1 2 3 John 1234 2345 5678 James 3456 6789 Jim 4567 I know I can find a specific name and corresponding article number in the table using VLOOKUP but this will only return a single article, I assume the last one. How can I get VLOOKUP (or match or index) to return multiple indices? Then I could tell it to return me the article at the 1st, 2nd, 3rd,... position. It should all be implemented in a dynamic worksheet way, that is without VBA. Once I update/change table1 and then press F9 it should update table2. Any help is appreciated. Thanks, Tom |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning multiple indices for a lookup
If your data is in A1:B7 (row 1 is a header), cell F2 = John, try this in G2:
=IF(COLUMNS($G2:G2)<=COUNTIF($A$2:$A$7,$F2),SMALL( IF($A$2:$A$7=$F2,$B$2:$B$7),COLUMNS($G2:G2)),"") array entered (Cntrl+Shift+Enter). Then copy across and down. Looking back at your post - since you have a counter set up (lets say in cell G1), the formula becomes: =IF(G$1<=COUNTIF($A$2:$A$7,$F2),SMALL(IF($A$2:$A$7 =$F2,$B$2:$B$7),G$1),"") array entered - copied across and down. " wrote: Hi, I have a table which contains a name in the first column and an article-number in the second column. One person can have multiple articles but the article numbers are unique. I am now trying to generate a table showing which person has which articles, sorted per name whereby the name is given for each line (is already sorted as needed). In the output I also have a 'counter' going across for easier readability. I'll try and give an example: Table1 (exists): Name Article John 1234 John 2345 James 3456 Jim 4567 John 5678 James 6789 Table2 (needed): 1 2 3 John 1234 2345 5678 James 3456 6789 Jim 4567 I know I can find a specific name and corresponding article number in the table using VLOOKUP but this will only return a single article, I assume the last one. How can I get VLOOKUP (or match or index) to return multiple indices? Then I could tell it to return me the article at the 1st, 2nd, 3rd,... position. It should all be implemented in a dynamic worksheet way, that is without VBA. Once I update/change table1 and then press F9 it should update table2. Any help is appreciated. Thanks, Tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning multiple indices for a lookup
"Name" & "Article" are defined name ranges in column A & B
D2: holds John D3: holds James D4: holds Jim In E2: =IF(ISERR(SMALL(IF(Name=$D2,ROW(INDIRECT("1:"&ROWS (Name)))),COLUMNS($A:A))),"",INDEX(Article,SMALL(I F(Name=$D2,ROW(INDIRECT("1:"&ROWS(Name)))),COLUMNS ($A:A)))) ctrl+shift+enter, not just enter copy across and down " wrote: Hi, I have a table which contains a name in the first column and an article-number in the second column. One person can have multiple articles but the article numbers are unique. I am now trying to generate a table showing which person has which articles, sorted per name whereby the name is given for each line (is already sorted as needed). In the output I also have a 'counter' going across for easier readability. I'll try and give an example: Table1 (exists): Name Article John 1234 John 2345 James 3456 Jim 4567 John 5678 James 6789 Table2 (needed): 1 2 3 John 1234 2345 5678 James 3456 6789 Jim 4567 I know I can find a specific name and corresponding article number in the table using VLOOKUP but this will only return a single article, I assume the last one. How can I get VLOOKUP (or match or index) to return multiple indices? Then I could tell it to return me the article at the 1st, 2nd, 3rd,... position. It should all be implemented in a dynamic worksheet way, that is without VBA. Once I update/change table1 and then press F9 it should update table2. Any help is appreciated. Thanks, Tom |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning multiple indices for a lookup
JMB wrote:
If your data is in A1:B7 (row 1 is a header), cell F2 = John, try this in G2: =IF(COLUMNS($G2:G2)<=COUNTIF($A$2:$A$7,$F2),SMALL( IF($A$2:$A$7=$F2,$B$2:$B$7),COLUMNS($G2:G2)),"") array entered (Cntrl+Shift+Enter). Then copy across and down. Looking back at your post - since you have a counter set up (lets say in cell G1), the formula becomes: =IF(G$1<=COUNTIF($A$2:$A$7,$F2),SMALL(IF($A$2:$A$7 =$F2,$B$2:$B$7),G$1),"") array entered - copied across and down. Thomas wrote: Hi, I have a table which contains a name in the first column and an article-number in the second column. One person can have multiple articles but the article numbers are unique. I am now trying to generate a table showing which person has which articles, sorted per name whereby the name is given for each line (is already sorted as needed). In the output I also have a 'counter' going across for easier readability. I'll try and give an example: Table1 (exists): Name Article John 1234 John 2345 James 3456 Jim 4567 John 5678 James 6789 Table2 (needed): 1 2 3 John 1234 2345 5678 James 3456 6789 Jim 4567 I know I can find a specific name and corresponding article number in the table using VLOOKUP but this will only return a single article, I assume the last one. How can I get VLOOKUP (or match or index) to return multiple indices? Then I could tell it to return me the article at the 1st, 2nd, 3rd,... position. It should all be implemented in a dynamic worksheet way, that is without VBA. Once I update/change table1 and then press F9 it should update table2. Any help is appreciated. Thanks, Tom Hi, Thanks a lot for your help guys, it worked just right. I now have the table I need. Unfortunately I just realised that my article number can also contain letters. Therefore, the SMALL function is not working. As a solution I am thinking to get the cell reference instead of the content, get the rows of the reference using SMALL and then use that index to get the content. So, the idea is there but I am not able to put it into a function that will work. Could anyone help me to get the same function working but with alphanumerical article numbers? Of course it can be done differently than my simple idea. I'm sure there are more sophisticated solutions. Thanks for all the help, Thomas |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning multiple indices for a lookup
"Thomas Toth" wrote in message . .. JMB wrote: If your data is in A1:B7 (row 1 is a header), cell F2 = John, try this in G2: =IF(COLUMNS($G2:G2)<=COUNTIF($A$2:$A$7,$F2),SMALL( IF($A$2:$A$7=$F2,$B$2:$B$7),COLUMNS($G2:G2)),"") array entered (Cntrl+Shift+Enter). Then copy across and down. Looking back at your post - since you have a counter set up (lets say in cell G1), the formula becomes: =IF(G$1<=COUNTIF($A$2:$A$7,$F2),SMALL(IF($A$2:$A$7 =$F2,$B$2:$B$7),G$1),"") array entered - copied across and down. Thomas wrote: Hi, I have a table which contains a name in the first column and an article-number in the second column. One person can have multiple articles but the article numbers are unique. I am now trying to generate a table showing which person has which articles, sorted per name whereby the name is given for each line (is already sorted as needed). In the output I also have a 'counter' going across for easier readability. I'll try and give an example: Table1 (exists): Name Article John 1234 John 2345 James 3456 Jim 4567 John 5678 James 6789 Table2 (needed): 1 2 3 John 1234 2345 5678 James 3456 6789 Jim 4567 I know I can find a specific name and corresponding article number in the table using VLOOKUP but this will only return a single article, I assume the last one. How can I get VLOOKUP (or match or index) to return multiple indices? Then I could tell it to return me the article at the 1st, 2nd, 3rd,... position. It should all be implemented in a dynamic worksheet way, that is without VBA. Once I update/change table1 and then press F9 it should update table2. Any help is appreciated. Thanks, Tom Hi, Thanks a lot for your help guys, it worked just right. I now have the table I need. Unfortunately I just realised that my article number can also contain letters. Therefore, the SMALL function is not working. As a solution I am thinking to get the cell reference instead of the content, get the rows of the reference using SMALL and then use that index to get the content. So, the idea is there but I am not able to put it into a function that will work. Could anyone help me to get the same function working but with alphanumerical article numbers? Of course it can be done differently than my simple idea. I'm sure there are more sophisticated solutions. Thanks for all the help, Thomas Try this (based on JMB's formula): =IF(COLUMNS($G2:G2)<=COUNTIF($A$2:$A$7,$F2),INDEX( $B$2:$B$7,SMALL(IF($A$2:$A$7=$F2,ROW(B$2:B$7)-MIN(ROW(B$2:B$7))+1),COLUMNS($G2:G2))),"") Still an array formula. Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup returning several cells | Excel Discussion (Misc queries) | |||
Indices - Agreed Standards? | New Users to Excel | |||
Lookup Returning Multiple Results | Excel Discussion (Misc queries) | |||
Returning multiple corresponding values using lookup in a list | Excel Discussion (Misc queries) | |||
Returning multiple instances of the same lookup value | Excel Worksheet Functions |