Drop down list with vlookup in Excel

问题:

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.

回答1:

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.

Data validation tool showing:

回答2:

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..

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