想要从索引数组中获取多个值到excel中的单个单元格中

I am trying to find a way to get multiple values from an array to display in one cell

我试图找到一种方法，从数组中获取多个值，并在一个单元格中显示

Say for example I have the two columns as below

例如，我有下面这两列

a 1

一个1

b 2

c 1

d 3

e 2

I want all the values form the first column where the second column is 1

我想要所有的值从第一列开始第二列是1

vlookup and index with match both only provide the first matching instance, is there a way to do this with a function or does it have to be created in a macro with VBA?

vlookup和index with match都只提供第一个匹配实例，是否有一种方法可以用一个函数来实现这一点，还是必须用VBA在宏中创建它?

Thanks

谢谢

3 个解决方案

#1

If you want all the results to be shown in ONE cell you could use that VBA formula:

如果你希望所有的结果显示在一个单元中，你可以使用VBA公式:

'r is the range of cells that you have a value to look for
'v is the value you are looking for
Function getValues(r As Range, v As Variant)

Dim c As Range

getValues = ""

For Each c In r
If c.Value = v Then
    If getValues = "" Then
        'Offset(0,-1) will give you value from previous coulmn
        getValues = c.Offset(0, -1).Value
    Else
        getValues = getValues & "," & c.Offset(0, -1).Value
    End If
End If
Next c

End Function

Use example: in cell C1 enter this =getValues(B1:B5,1)

使用示例:在单元格C1中输入this =getValues(B1:B5,1)

#2

Considering your data is in range A1:B5 and Cell C1 contains the number you are looking for.

考虑到您的数据在A1:B5范围内，单元格C1包含您要查找的数字。

Enter the following array formula in Cell D1 and drag/copy down till the row as required.

在单元格D1中输入以下数组公式，并按要求拖/复制到下一行。

=IFERROR(INDEX($A$1:$A$5, SMALL(IF($C$1=$B$1:$B$5, ROW($B$1:$B$5)-MIN(ROW($B$1:$B$5))+1, ""), ROW(A1))), "")

Being an array formula you'll have to commit above formula by pressing Ctrl+Shift+Enter.

作为一个数组公式，您必须通过按Ctrl+Shift+Enter提交上面的公式。

#3

Using helper columns this is possible with formulas alone.

仅使用帮助列就可以使用公式。

Formulas:

公式:

In D1:

在D1:

=""

In D2 downwards:

在D2向下:

=IF(B2=$B$1,D1&A2&", ",D1)

In C1:

在C1:

=LEFT(LOOKUP(2,1/(D:D<>0),D:D),LEN(LOOKUP(2,1/(D:D<>0),D:D))-2)

#1