VLOOKUP or Matching Question

laneglobal · Apr 26, 2011

I am doing a VLOOKUP by name but there happens to be multiple items for each name in my list that contains thousands of names by the way. I have a drop box for the name and would like to lookup to list the multiple items for that name below the name. VLOOKUP only lists the first value. Any way to list all values?

 <table class="MsoNormalTable" style="width: 331pt; margin-left: 5pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="441"> <tbody><tr style="height: 15pt;"> <td style="width: 21pt; border: 1pt solid rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot] [/FONT]

</td> <td style="width: 81pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: rgb(165, 165, 165) rgb(165, 165, 165) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108">

[FONT=&quot]A[/FONT]

</td> <td style="width: 124pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: rgb(165, 165, 165) rgb(165, 165, 165) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165">

[FONT=&quot]B[/FONT]

</td> <td style="width: 105pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: rgb(165, 165, 165) rgb(165, 165, 165) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140">

[FONT=&quot]C[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]1[/FONT]

</td> <td style="width: 81pt; border-width: 1pt medium medium 1pt; border-style: solid none none solid; border-color: black -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot]Emp. #[/FONT]
</td> <td style="width: 124pt; border-width: 1pt medium medium; border-style: solid none none; border-color: black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]Name[/FONT]
</td> <td style="width: 105pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: black black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot]Dept[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]2[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot]A204[/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]Smith, Joe[/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot]Accounting[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]3[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot]A206[/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]Don, Jane[/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot]Operations[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]4[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot]A208[/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]Miller, Bob[/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot]MIS[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]5[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot]A210[/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]White, Fred[/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot]Accounting[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]6[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot]A212[/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]Miller, Bob[/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot]Manufacturing[/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]7[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot] [/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot] [/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot] [/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]8[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot] [/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot] [/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot] [/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]9[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot]Name[/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]Emp. #[/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot] [/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]10[/FONT]

</td> <td style="width: 81pt; border-width: medium medium medium 1pt; border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot]Miller, Bob[/FONT]
</td> <td style="width: 124pt; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]MIS[/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color black -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot] [/FONT]
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(165, 165, 165) rgb(165, 165, 165); background: none repeat scroll 0% 0% rgb(216, 216, 216); padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="28">

[FONT=&quot]11[/FONT]

</td> <td style="width: 81pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color black black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="108"> [FONT=&quot] [/FONT]
</td> <td style="width: 124pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color black; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="165"> [FONT=&quot]Manufacturing[/FONT]
</td> <td style="width: 105pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="140"> [FONT=&quot] [/FONT]
</td> </tr> </tbody></table>

tweedle · Apr 26, 2011

I think I would apply an advanced filter to the data you want to filter.
Within that advanced filter, you can place the result of your drop-box selection and execute the filter, thereby listing the multiple items for that name.

laneglobal · Apr 26, 2011

Yeah I know I can do the advanced filter but am really trying to figure out how to lookup the second item that it finds and so on. If only vlookup/hlookup had an additional option to tell it to find second match, third match, and so on...

laneglobal · Apr 26, 2011

I figured out how to do it with a VLOOKUP, COUNTIF, CONCATENATE, AND IF/ISNA statement. If anyone else wants to know let me know and i'll post it it would take me a min to make a walk through which is why I don't just post it...

VLOOKUP or Matching Question

laneglobal

New Member

Excel Facts

tweedle

Well-known Member

laneglobal

New Member

laneglobal

New Member

Similar threads

Forum statistics

Share this page

VLOOKUP or Matching Question

laneglobal

New Member

Excel Facts

tweedle

Well-known Member

laneglobal

New Member

laneglobal

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock