Ahh! Now I understand the objective. Those commas threw me off last time.
Ok, with €śWP€ť in A1 and €śPMC€ť in B1, and A2:B10 as such:
1 1
2 2
3 3
4 4
5 7
5 8
5 9
5 10
5 52
Enter this function into D2:
=IF(ROWS(B$2:B2)<=COUNTIF($A$2:$A$20,$C$2),INDEX($ B$2:$B$20,SMALL(IF($A$2:$A$20=$C$2,ROW($A$2:$A$20)-ROW($C$2)+1),ROWS(B$2:B2))),"")
Commit it with Ctrl+Shift+Enter (not just enter) and finally€¦ in C2 place
the value that you want to lookup in Column A.
I put 5 in C2 and got the following in D2:D6:
7
8
9
10
52
Regards,
Ryan--
PS, the arrangement of data is slightly different that what you described;
not sure if you can have that kind of layout...with all the commas between
the values...
--
RyGuy
"Curt D." wrote:
I appreciate the idea ryguy but I need it in another cell in a different
column. Any ideas on how to do this?
CD
"ryguy7272" wrote:
Have you tried using a Pivot Table?
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
Regards,
Ryan---
--
RyGuy
"Curtd" wrote:
What I am trying to do is be able to lookup one number in Column A (Header is
WP), and give me all the matching results from Column B (Header is PMC), and
put display all the results in Column C. I need to do this without
downloading anymore addins also, thanks for your help in advance. Here is an
example:
WP PMC
1 1 1
2 2 2
3 3 3
4 4 4
5 7 7,8,9,10,52
5 8 7,8,9,10,52
5 9 7,8,9,10,52
5 10 7,8,9,10,52
5 52 7,8,9,10,52