Vlookup to Return Multiple Rows

[k1008]

Hello there,

I need to vlook up to return mutiple rows from Column B with the same value in Column A. For instance, you have mutiple phone # (up to 8) listed under your name in Column B in a "big yellow book" sorted by phone numbers. In other words, I need to vlookup and list your name in Column A and corresponding phone # in Colum B to return from vlookup.

Before Vlookup
101 des1
101 des2
105 des105-1
101 des3
105 des105-2
101 des4

After Vlookup

101 des1
101 des2
101 des3
101 des4


Thx,

Jimmy

 

[pedie]

Looking at your table i think you dont have to use formula for that. Sorting it will give you exactly the same look of the expected result table...

 

[Haseeb Avarakkan]

Or with formula Try;

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Before</td><td style="font-weight: bold;;">Vlookup</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Lookup Value</td><td style="text-align: right;;">101</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">101</td><td style=";">des1</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Total Count</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">101</td><td style=";">des2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">105</td><td style=";">des105-1</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">After</td><td style="font-weight: bold;;">Vlookup</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">101</td><td style=";">des3</td><td style="text-align: right;;"></td><td style="text-align: right;;">101</td><td style=";">des1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">105</td><td style=";">des105-2</td><td style="text-align: right;;"></td><td style="text-align: right;;">101</td><td style=";">des2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">101</td><td style=";">des4</td><td style="text-align: right;;"></td><td style="text-align: right;;">101</td><td style=";">des3</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">101</td><td style=";">des4</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=COUNTIF(<font color="Blue">A:A,E1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">D$5:D5</font>)<=E$2,$E$1,""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">E$5:E5</font>)<=E$2,INDEX(<font color="Red">B:B,SMALL(<font color="Green">IF(<font color="Purple">$A$2:$A$100=E$1,ROW(<font color="Teal">$A$2:$A$100</font>)</font>),ROWS(<font color="Purple">E$5:E5</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Copied down...

 

[k1008]

Hello Haseeb,

Thank you so much for your help. It worked well for both Excel 2007 and 2010.

Can you kindly explain the logic and purposes for formulas in D5 and E5?

Again, many thanks,

Jimmy

 

[Sanador]

I've been struggling trying to adapt this formula to my needs. It seems perfect, but for whatever reason, I cannot seem to get an accurate result. Similar to Jimmy above, I am attempting to list all the varsity sports played by individual students. To simplify things, my data looks something like this:

Sheet: ROSTER

Col. B-----Col. H-----Col. AB-----Col. AH-----Col. AN
YEAR-----NAME-----FALL SPORT-----WINTER SPORT-----SPRING SPORT
09-10-----Joe-----MSC-----MBB J-----BS
09-10-----Jim-----MCC-----MBB J-----
10-11-----Joe-----MSC-----MBB-----BS

I've created a pivot table that provides (in column C of a separate sheet) the count of varsity sports for each student/season. For example, Joe would have 2 Fall seasons, 1 Winter, and 2 Spring while Jim would have 1 Winter. To my understanding, this number would be used as the "Total Count" in the previous example. The result I'm after is also the same. I'd like:

Joe-----MSC-----2-----MBB-----1-----BS-----2

Because I'm attempting to do this horizontally rather than vertically, I thought the appropriate formula might be:

=IF(COLUMNS($E47:E47)<=$C47,INDEX(ROSTER!$AB$17:$AB$3000,SMALL(MATCH($A47,ROSTER!$H$17:$H$3000,0),COLUMNS($E47:E47)),1),"")

I hope that's somewhat clear. Any help would be appreciated.

 

[siddsg]

Thanks, Haseeb. I was struggling with this for a long time and your solution has been most useful!