Multiple Results for Vlookup in one column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Curtd - 11 Mar 2008 17:43 GMT

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

Reply to this Message

ryguy7272 - 12 Mar 2008 18:26 GMT

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---

Signature

RyGuy

> 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
[quoted text clipped - 12 lines]
> 5 10 7,8,9,10,52
> 5 52 7,8,9,10,52

Reply to this Message

Curt D. - 14 Mar 2008 00:21 GMT

I appreciate the idea ryguy but I need it in another cell in a different
column. Any ideas on how to do this?

CD

> Have you tried using a Pivot Table?
> http://peltiertech.com/Excel/Pivots/pivottables.htm
[quoted text clipped - 22 lines]
> > 5 10 7,8,9,10,52
> > 5 52 7,8,9,10,52

Reply to this Message

ryguy7272 - 14 Mar 2008 20:04 GMT

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...

Signature

RyGuy

> I appreciate the idea ryguy but I need it in another cell in a different
> column. Any ideas on how to do this?
[quoted text clipped - 27 lines]
> > > 5 10 7,8,9,10,52
> > > 5 52 7,8,9,10,52

Reply to this Message