VLOOKUP返回多个匹配项 [英] VLOOKUP to return multiple matches
问题描述
我想问问在使用vlookup时是否有一种方法/公式/vba返回多个值?例如，我vlookup一个数据，当该数据有多个值要返回时，它将返回其他值.谢谢.
I want to ask if there's a way/formula/vba to return multiple values when using vlookup? For example, I vlookup a data and when that data has multiple values to return, it will return the other values. Thanks.
推荐答案
对于这种通用的东西，只需使用Google.
For something as generic as this, just use Google.
Step #1) www.google.com
Step #2) get your answer in less time than it takes you to post here.
返回一个查找值的多个对应值
Return MULTIPLE corresponding values for ONE Lookup Value
Excel VLOOKUP函数在表数组的第一列中搜索值(即Lookup_value)，并从表数组的另一列返回同一行中的值.如果多次出现Lookup值，该函数将搜索第一次出现的Lookup值，并从另一列的同一行中返回相应的值.
The Excel VLOOKUP Function searches for a value (ie. Lookup_value) in the first column of a table array and returns a value in the same row from another column in the table array. In case of multiple occurrences of the Lookup value, the function searches the first occurrence of the Lookup value, and returns the corresponding value in the same row from another column.
如果您想返回多个相应的值，对于一个具有多个出现次数的Lookup值，我们将说明如何使用INDEX，SMALL，IF和Amp来完成.ROW excel功能如下.
In case you want to return multiple corresponding values, for the one Lookup value which has multiple occurrences, we show how it can be done using INDEX, SMALL, IF & ROW excel functions, as follows.
考虑表数组("A2:B8")，您要在其中查找多次出现的A列中的值"Apples"，并在B列中返回所有对应的值.
Consider the table array ("A2:B8"), in which you want to lookup the value "Apples" in column A which has multiple occurrences, and return all corresponding values in column B.
在单元格A11中输入查找值"Apples".在单元格B11中，输入以下公式作为数组公式(CTRL + SHIFT + ENTER)，并将其向下复制到同一列B中的7行中(即，作为表数组"A2中的记录数"的次数):B8.多个对应的值(查找值"Apples"的值)将垂直复制，从单元格B11到B17.请参见表1.
Enter the lookup value "Apples" in cell A11. In cell B11, enter below formula, as an array formula (CTRL+SHIFT+ENTER), and copy it downward in the same column B, in 7 rows (ie. number of times as the number of records in the table array "A2:B8". Multiple corresponding values (of the lookup value "Apples") will get copied vertically, starting from cell B11 till B17. Refer Table 1.
= INDEX($ B $ 2:$ B $ 8，小(IF($ A $ 11 = $ A $ 2:$ A $ 8，ROW($ A $ 2:$ A $ 8)-ROW($ A $ 2)+1)，ROW(1:1)))
=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1)))
