在 Excel 中使用 vlookup 下拉列表 [英] Drop down list with vlookup in Excel

问题描述

我需要从 (B2, B3, B4,...) auto 中的零件下拉列表中进行选择使用该部件的供应商填充另一个单元格(D2、D3、D4、...).我可以使用 Parts_Data 表的简单 VLOOKUP 来完成大部分工作:

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)

不幸的是，有些零件可以从多个供应商处购买.When one of those multi vendor parts is selected, I would like there to be another drop down in D2, D3, D4, ...这允许从两个或三个中选择正确的供应商.

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.

我想出了一个笨拙的解决方案，使用 INDIRECT 并创建相关下拉列表，其中大多数部件在第二个下拉列表中只有一个制造商条目.如果可能的话，我想为大多数选择消除这种额外的鼠标点击.

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.

推荐答案

首先，添加一个将 Parts 映射到 Vendors 的表.注意:零件到供应商表必须按零件排序.

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)

如屏幕截图所示.请注意，J 列的单元格引用已更改为混合引用，因此该行是相对的.然后您可以填写此信息，数据验证将使用零件编号来确定供应商列表.

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.

数据验证工具显示:

这篇关于在 Excel 中使用 vlookup 下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！