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
|