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

问题描述

我需要从（ B2 ， B3 ， B4 ，...）自动填充另一个单元格（ D2 ， D3 ， D4 ，...）与该零件的供应商联系。我可以用一个简单的 VLOOKUP 来完成 Parts_Data 表的大部分工作：

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)

不幸的是，某些零件可以从多个供应商处购买。当选择这些多供应商的部件之一，我想在那里是另一个下拉在 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.

推荐答案

首先，添加一个表将零件映射到供应商。 注意：零件供应商表必须按零件排序。

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屋！