Drop Down and vlookup

nycard64 · Jan 26, 2010

I have a sheet where I am using a Drop down and when I select one of the items in the list I want to return all values that match from another table.

My sheet looks like this right now under the EE, PE and Maint columns I have more then one entry but only get one to return.

ex: under EE I should return Gene and then below this name Stan.

A B C D

<table x:str="" style="border-collapse: collapse; width: 250pt;" border="0" cellpadding="0" cellspacing="0" width="333"><col style="width: 48pt;" width="64"> <col style="width: 58pt;" width="77"> <col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td class="xl25" style="width: 58pt;" width="77">Drop Down</td> <td class="xl26" style="width: 48pt;" width="64">EE</td> <td class="xl26" style="width: 48pt;" width="64">PE</td> <td class="xl26" style="width: 48pt;" width="64">MAINT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">Vendor:</td> <td>LAM</td> <td class="xl24">Gene</td> <td class="xl24">Warwick</td> <td class="xl24">Mike</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> <td class="xl24" x:err="#N/A">#N/A</td> </tr> </tbody></table>

hiker95 · Jan 26, 2010

nycard64,

Can we have separate screenshots of your drop-down area, and the table?

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

nycard64 · Jan 27, 2010

<title>Excel Jeanie HTML</title>My drop down in located in Column B and I need to return names under D, E and F from the list below in Sheet 2. So in column D for EE in rows 3 = Gene, row 4 = Stan, same for E and F. If you need more clarification then let me know. Thanks

Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 77px;"> <col style="width: 77px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td> </td> <td style="background-color: rgb(153, 204, 255); font-weight: bold;">Drop Down</td> <td style="background-color: rgb(153, 204, 255); font-weight: bold;"> </td> <td style="text-align: center; background-color: rgb(153, 204, 255); font-weight: bold;">EE</td> <td style="text-align: center; background-color: rgb(153, 204, 255); font-weight: bold;">PE</td> <td style="text-align: center; background-color: rgb(153, 204, 255); font-weight: bold;">MAINT</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="font-weight: bold;">Vendor:</td> <td style="text-align: center; font-weight: bold;">LAM</td> <td style="font-weight: bold;"> </td> <td style="text-align: center;">Gene</td> <td style="text-align: center;">Warwick</td> <td style="text-align: center;">Jeff</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td> </td> <td> </td> <td> </td> <td style="text-align: center;">Gene</td> <td style="text-align: center;">Warwick</td> <td style="text-align: center;">Jeff</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>D3</td> <td>=VLOOKUP(B3,$J$3:$K$9,2,FALSE)</td></tr> <tr> <td>E3</td> <td>=VLOOKUP(B3,$L$3:$M$10,2,FALSE)</td></tr> <tr> <td>F3</td> <td>=VLOOKUP(B3,$N$3:$O$6,2,FALSE)</td></tr> <tr> <td>D4</td> <td>=VLOOKUP(B3,$J$3:$K$9,2,FALSE)</td></tr> <tr> <td>E4</td> <td>=VLOOKUP(B3,$L$3:$M$10,2,FALSE)</td></tr> <tr> <td>F4</td> <td>=VLOOKUP(B3,$N$3:$O$6,2,FALSE)</td></tr></tbody></table></td></tr></tbody></table>

<title>Excel Jeanie HTML</title>Sheet2

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="font-weight: bold;">Vendor</td> <td style="font-weight: bold;">EE</td> <td style="font-weight: bold;">Vendor</td> <td style="font-weight: bold;">PE</td> <td style="font-weight: bold;">Vendor</td> <td style="font-weight: bold;">MAINT</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>TEL</td> <td>Carl</td> <td>TEL</td> <td>Ken</td> <td>TEL</td> <td>Bob</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>TEL</td> <td>Marc</td> <td>LAM</td> <td>Warwick</td> <td>LAM</td> <td>Jeff</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>LAM</td> <td>Gene</td> <td>LAM</td> <td>Larry</td> <td>AMAT</td> <td>Ted</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>LAM</td> <td>Stan</td> <td>LAM</td> <td>Kelly</td> <td>Shibaura</td> <td>Mike</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>AMAT</td> <td>Todd</td> <td>TEL</td> <td>Hong</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>Axcelis</td> <td>Barry</td> <td>TEL</td> <td>Kevin</td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>Shibaura</td> <td>Carl</td> <td>AMAT</td> <td>Eric</td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td> </td> <td> </td> <td>Shibaura</td> <td>Kelly</td> <td> </td> <td> </td></tr></tbody></table>

hiker95 · Jan 27, 2010

nycard64,

In order to get the 2nd, 3rd, 4th,..., item in a column with VLOOKUP, I used a User Defined Function "VlookupNth", by "Jonmo", from here:
http://www.mrexcel.com/board2/viewtopic.php?t=302681&sid=81cc9a966bb7e6a4ca2c63523b1396b7

Excel Workbook

J

K

L

M

N

O

2

Vendor

EE

Vendor

PE

Vendor

MAINT

3

TEL

Carl

TEL

Ken

TEL

Bob

4

TEL

Marc

LAM

Warwick

LAM

Jeff

5

LAM

Gene

LAM

Larry

AMAT

Ted

6

LAM

Stan

LAM

Kelly

Shibaura

Mike

7

AMAT

Todd

TEL

Hong

8

Axcelis

Barry

TEL

Kevin

9

Shibaura

Carl

AMAT

Eric

10

Shibaura

Kelly

11

Sheet2

Excel Workbook

A

B

C

D

E

F

2

Drop Down

EE

PE

MAINT

3

Vendor:

LAM

4

5

6

7

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Function
1. Copy the below Function code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Code:

Option Explicit
Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, Optional Nth As Long = 1)
'
' jonmo1
' http://www.mrexcel.com/board2/viewtopic.php?t=302681&sid=81cc9a966bb7e6a4ca2c63523b1396b7
'
'Similar to Vlookup, but returns the Nth value found from the top of myrange.
'Not necessarily the First.
'The TRUE/FALSE argument usually found in Vlookup is assumed FALSE in this function.  Data does NOT need
'to be sorted, and it searches for EXACT match.
'if ColRef is omitted, uses the number of columns in myrange
'if Nth is omitted, returns the first value found
'
Dim Count, i As Long
Dim MySheet As Worksheet
Count = 0
Set MySheet = Sheets(MyRange.Parent.Name)
If ColRef = 0 Then ColRef = MyRange.Columns.Count
For i = MyRange.Row To MyRange.Row + MyRange.Rows.Count - 1
  If MySheet.Cells(i, MyRange.Column).Value = MyVal Then
    Count = Count + 1
    If Count = Nth Then
      VlookupNth = MySheet.Cells(i, MyRange.Column + ColRef - 1).Value
      Exit Function
    End If
  End If
Next i
VlookupNth = ""
End Function

nycard64 · Jan 28, 2010

Worked perfectly....thank you very much my boss will be happy now

Drop Down and vlookup

nycard64

Board Regular

Excel Facts

hiker95

Well-known Member

nycard64

Board Regular

hiker95

Well-known Member

nycard64

Board Regular

Similar threads

Forum statistics

Share this page

Drop Down and vlookup

nycard64

Board Regular

Excel Facts

hiker95

Well-known Member

nycard64

Board Regular

hiker95

Well-known Member

nycard64

Board Regular

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