Returning multiple indices for a lookup

[thomas.toth]

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

 

[Guest]

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.

 

[Guest]

"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(IF(Name=$D2,ROW(INDIRECT("1:"&ROWS(Name)))),COLUMNS($A:A))))

ctrl+shift+enter, not just enter
copy across and down

 

[Thomas Toth]

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.

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

 

[T. Valko]

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