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
|