Drop Down List Using Vlookup

J

Jess709

New Member
Joined
Apr 24, 2015
Messages
20
I have an existing spreadsheet which has product information along with sales information. The sales information is grouped by manufacturer number. My end users want to also see the corresponding sku (i.e. product ID); however there are some manufacturer numbers which have more than 1 sku, so they requested that I provide a drop down list of the corresponding skus for each manufacturer number. To further complicate things the manufacturer number is not necessarily unique across all vendors. To solve this in my original spreadsheet I used a concatenate formula.

I've read a lot of instructions about dependent drop down lists and named ranges; however I'm struggling with finding a simple step by step on how to accomplish what I need done.

My situation differs from many that I've seen in that I only need 1 column of drop downs. My manufacturer number (MFR#) is static and doesn't need a drop down.

Let me know if there are any other questions, I'm pretty new at this.

First spreadsheet is main tab in which I need to have drop downs for skus.

Excel 2013 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD="bgcolor: #B4C6E7"]Concatenate[/TD]
[TD="bgcolor: #B4C6E7"]Vendor #[/TD]
[TD="bgcolor: #B4C6E7"]MFR #[/TD]
[TD="bgcolor: #B4C6E7"]Sku #[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD]
8645D93114​
[/TD]
[TD]8645[/TD]
[TD]D93114[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]
861184895/OLD#83864​
[/TD]
[TD]8611[/TD]
[TD]84895/OLD#83864[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD]
7987RS100AW-BLK​
[/TD]
[TD]7987[/TD]
[TD]RS100AW-BLK[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD]
860476068-CHA​
[/TD]
[TD]8604[/TD]
[TD]76068-CHA[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD]
860476893EW-NVBK​
[/TD]
[TD]8604[/TD]
[TD]76893EW-NVBK[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD]
8627G008 /OLD#G8845​
[/TD]
[TD]8627[/TD]
[TD]G008 /OLD#G8845[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8​
[/TD]
[TD]
8020B180DST /B18DST​
[/TD]
[TD]8020[/TD]
[TD]B180DST /B18DST[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9​
[/TD]
[TD]
8201THS01-2X​
[/TD]
[TD]8201[/TD]
[TD]THS01-2X[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
10​
[/TD]
[TD]
8633402​
[/TD]
[TD]8633[/TD]
[TD]402[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
11​
[/TD]
[TD]
861184890/OLD#83865​
[/TD]
[TD]8611[/TD]
[TD]84890/OLD#83865[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
12​
[/TD]
[TD]
8020100069-479 DISC​
[/TD]
[TD]8020[/TD]
[TD]100069-479 DISC[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
13​
[/TD]
[TD]
8337RM2035200 MENS​
[/TD]
[TD]8337[/TD]
[TD]RM2035200 MENS[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
14​
[/TD]
[TD]
860476887EW-BLK​
[/TD]
[TD]8604[/TD]
[TD]76887EW-BLK[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
15​
[/TD]
[TD]
946840135 ITASCA​
[/TD]
[TD]94[/TD]
[TD]6840135 ITASCA[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
16​
[/TD]
[TD]
860210017424​
[/TD]
[TD]8602[/TD]
[TD]10017424[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
17​
[/TD]
[TD]
805T347​
[/TD]
[TD]805[/TD]
[TD]T347[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
18​
[/TD]
[TD]
8633438​
[/TD]
[TD]8633[/TD]
[TD]438[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: All MFR#[/TD]
[/TR]
</tbody>[/TABLE]

2nd spreadsheet is my 2nd tab from which I would pull the skus
Excel 2013 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD="bgcolor: #B4C6E7"]Concatenate[/TD]
[TD="bgcolor: #B4C6E7"]Vendor #[/TD]
[TD="bgcolor: #B4C6E7"]MFR #[/TD]
[TD="bgcolor: #B4C6E7"]Sku #[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD]0000010000[/TD]
[TD]0000010000[/TD]
[TD][/TD]
[TD]000000001[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD]0000020000[/TD]
[TD]0000020000[/TD]
[TD][/TD]
[TD]000000002[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD]0000030000[/TD]
[TD]0000030000[/TD]
[TD][/TD]
[TD]000000003[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD]0000040000[/TD]
[TD]0000040000[/TD]
[TD][/TD]
[TD]000000004[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD]0000050000[/TD]
[TD]0000050000[/TD]
[TD][/TD]
[TD]000000005[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD]0000060000[/TD]
[TD]0000060000[/TD]
[TD][/TD]
[TD]000000006[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8​
[/TD]
[TD]0000090000[/TD]
[TD]0000090000[/TD]
[TD][/TD]
[TD]000000009[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
9​
[/TD]
[TD]0000010000[/TD]
[TD]0000010000[/TD]
[TD][/TD]
[TD]000000011[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
10​
[/TD]
[TD]0000020000DEPOSIT .05[/TD]
[TD]0000020000[/TD]
[TD]DEPOSIT .05[/TD]
[TD]000000021[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
11​
[/TD]
[TD]0000020000DEPOSIT .30[/TD]
[TD]0000020000[/TD]
[TD]DEPOSIT .30[/TD]
[TD]000000022[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
12​
[/TD]
[TD]0000020000DEPOSIT .60[/TD]
[TD]0000020000[/TD]
[TD]DEPOSIT .60[/TD]
[TD]000000023[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
13​
[/TD]
[TD]0000020000DEPOSIT 1.20[/TD]
[TD]0000020000[/TD]
[TD]DEPOSIT 1.20[/TD]
[TD]000000024[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
14​
[/TD]
[TD]0000020000[/TD]
[TD]0000020000[/TD]
[TD][/TD]
[TD]000000025[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
15​
[/TD]
[TD]0000020000DEPOSIT .20[/TD]
[TD]0000020000[/TD]
[TD]DEPOSIT .20[/TD]
[TD]000000026[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
16​
[/TD]
[TD]0000020000DEPOSIT 6.00[/TD]
[TD]0000020000[/TD]
[TD]DEPOSIT 6.00[/TD]
[TD]000000027[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
17​
[/TD]
[TD]0000020000DEPOSIT 18-PACK[/TD]
[TD]0000020000[/TD]
[TD]DEPOSIT 18-PACK[/TD]
[TD]000000028[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
18​
[/TD]
[TD]0000020000DEPOSIT 30-PACK[/TD]
[TD]0000020000[/TD]
[TD]DEPOSIT 30-PACK[/TD]
[TD]000000029[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
19​
[/TD]
[TD]0000005782[/TD]
[TD]0000005782[/TD]
[TD][/TD]
[TD]000000032[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
20​
[/TD]
[TD]0000007390[/TD]
[TD]0000007390[/TD]
[TD][/TD]
[TD]000000038[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
21​
[/TD]
[TD]0000007391[/TD]
[TD]0000007391[/TD]
[TD][/TD]
[TD]000000039[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
22​
[/TD]
[TD]0000009951SPECIAL ORDER[/TD]
[TD]0000009951[/TD]
[TD]SPECIAL ORDER[/TD]
[TD]000000041[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
23​
[/TD]
[TD]0000000930[/TD]
[TD]0000000930[/TD]
[TD][/TD]
[TD]000000043[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
24​
[/TD]
[TD]0000007747[/TD]
[TD]0000007747[/TD]
[TD][/TD]
[TD]000000047[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
25​
[/TD]
[TD]0000007110[/TD]
[TD]0000007110[/TD]
[TD][/TD]
[TD]000000056[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
26​
[/TD]
[TD]0000009182[/TD]
[TD]0000009182[/TD]
[TD][/TD]
[TD]000000062[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
27​
[/TD]
[TD]0000000995995[/TD]
[TD]0000000995[/TD]
[TD]995[/TD]
[TD]000000064[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
28​
[/TD]
[TD]0000000288BATTERY CORE$12[/TD]
[TD]0000000288[/TD]
[TD]BATTERY CORE$12[/TD]
[TD]000000288[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
29​
[/TD]
[TD]0000000346[/TD]
[TD]0000000346[/TD]
[TD][/TD]
[TD]000000346[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
30​
[/TD]
[TD]0000000352[/TD]
[TD]0000000352[/TD]
[TD][/TD]
[TD]000000352[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
31​
[/TD]
[TD]0000000997[/TD]
[TD]0000000997[/TD]
[TD][/TD]
[TD]000000621[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
32​
[/TD]
[TD]0000000995[/TD]
[TD]0000000995[/TD]
[TD][/TD]
[TD]000000995[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
33​
[/TD]
[TD]0000000997[/TD]
[TD]0000000997[/TD]
[TD][/TD]
[TD]000000997[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
34​
[/TD]
[TD]0000000997[/TD]
[TD]0000000997[/TD]
[TD][/TD]
[TD]000000998[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
35​
[/TD]
[TD]0000004636[/TD]
[TD]0000004636[/TD]
[TD][/TD]
[TD]000002839[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
36​
[/TD]
[TD]0000006666[/TD]
[TD]0000006666[/TD]
[TD][/TD]
[TD]000006666[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
37​
[/TD]
[TD]0000007473SPECIAL ORDER[/TD]
[TD]0000007473[/TD]
[TD]SPECIAL ORDER[/TD]
[TD]000007473[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
38​
[/TD]
[TD]0000008868SPECIAL ORDER[/TD]
[TD]0000008868[/TD]
[TD]SPECIAL ORDER[/TD]
[TD]000008868[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sku Info[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You must log in or register to reply here.

Similar threads

alm395
  • Question Question
Hiding Used Items in Drop Down List (multiple columns)
Replies
0
Views
581
alm395
alm395
E
  • Question Question
Can I Use a Formula with Data Validation>List
Replies
1
Views
515
iliace
iliace
A
  • Question Question
Dependent Drop down list that populates from a data set
Replies
0
Views
575
acousticlife81
A
S
  • Question Question
Data Validation Drop Down List using INDIRECT and VLOOKUP
Replies
6
Views
33K
T. Valko
T. Valko
V
  • Question Question
Select data from table using data from dynamic drop down list
Replies
4
Views
316
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top