Drop down list with vlookup in Excel

0
votes

I need to have a selection from a drop down list of parts in (B2, B3, B4,...) auto populate another cell (D2, D3, D4, ...) with the vendor for that part. I can accomplish most of that with a simple VLOOKUP for Parts_Data table:

=VLOOKUP($B2,Parts_Data,2,0)

Unfortunately some of the parts can be purchased from more than one vendor. When one of those multi vendor parts is selected, I would like there to be another drop down in D2, D3, D4, ... that allows the correct vendor out of the two or three to be selected.

I came up with a clunky solution using INDIRECT and creating dependent drop downs with most of the parts having just one entry for the manufacturer in the second drop down list. I would like to eliminate this extra mouse click for most of the selections if at all possible.

2
drop-down-menuexcel-formulavlookup
Use choose() to populate a set of cells used by vlookup. – Solar Mike
Could you expand on that a bit. Using the CHOOSE function seems to result in nothing but, "The list source must be a delimited list, or reference to a single row or column." errors when I try to use it to call the named range for that part. – James Ogle
@JamesOgle Did one of these answers help? – John F

2 Answers

1
votes

First, add a table that maps Parts to Vendors. NOTE:THE PART-TO-VENDORS TABLE MUST BE SORTED BY PART.

In the top cell of the column where you want to select the vendor based on the part, add a list data validation with a list source as a formula:

=OFFSET($G$4,MATCH($J5,$F$5:$F$10,0),0,COUNTIF($F$5:$F$10,$J5),1)

as shown in the screen grab. NOTE THE CELL REFERENCE TO COLUMN J HAS BEEN CHANGED TO A MIXED REFERENCE SO THE ROW IS RELATIVE. You can then fill this down and the Data Validation will use the Part number to determine the list of suppliers.

enter image description here

Data validation tool showing:

enter image description here

0
votes

Knocked this up to show you what is possible, may not be the best way or most elegant way - there may be ways of combining formulae so they are shorter but you should be able to take the technique in any direction you wish..

enter image description here

The droplist B is populated by the result of the choose function depending on which manufacturer is selected in droplist A...