Return list of all values that match criteria

I am trying to help my boss set up an Excel sheet but I am not too familiar.

I am looking to list all values of a cell that match a criteria.

Sheet 1 A B
1 Adam 4
2 Dave 4
3 Steve 3
4 Ryan 4

What I want is to return a list of all names with values in column B that equal 4.

So the result would look like this:

 A B
1 RESULTS Adam Dave Ryan

Any help would be greatly appreciated, thank you.

4 Answers

If you don't want a pivot table, you can use an array formula.

I'm using D4 to store the number you want to return (in this case names that match 4)..but you can edit as necessary:

=IFERROR(INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS($B$1:$B1))),"")

Enter with CTRL+SHIFT+ENTER and drag down.

A neat solution that I think may work for these scenarios:

=FILTER(A:A,B:B=D1,"") where D1 contains the number you want to filter by.

I feel that a simple INDEX and MATCH function would be much simpler:

=IFERROR(INDEX(A1:B4,1,MATCH($F$1,B1:B4,0)),"")

You can use this formula :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:B)/(B:B=4),ROW(1:1))),"")