Want to get multiple values from an index array into a single cell in excel

Question

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

b 2

c 1

d 3

e 2

I want all the values form the first column where the second column is 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?

Thanks

Answer 1

Using helper columns this is possible with formulas alone.

Formulas:

In D1:

=""

In D2 downwards:

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

In C1:

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

Answer 2

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

'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)

Answer 3

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

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

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