# 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
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
Dave
Ryan
``````

Any help would be greatly appreciated, thank you.

• what about a pivot table? You can set name as row headers, set values as filter, then filter to `4`. You can find several good tutorials on pivot tables online. – Máté Juhász Jan 19 '17 at 16:06
• You can just use a filter -- either the simple or advanced filter can accomplish that. – Ron Rosenfeld Jan 23 '17 at 1:44

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.

• without CSE `=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!\$B\$1:\$B\$4)/(Sheet1!\$B\$1:\$B\$4=4),ROW(1:1))),"")` – Scott Craner Jan 19 '17 at 17:25
• @ScottCraner - that's really nifty, thanks for that!! – BruceWayne Jan 19 '17 at 17:57
• @Jeeped not bad though!! Congrats on winning the car, that's so crazy...especially since you had the better flush! I bet that was a crazy feeling. Kudos and thanks for all of your help again! :D – BruceWayne Sep 10 '19 at 13:45
• @Jeeped - D'oh, meant to type full house. Haha, that's so crazy, I can't believe you kept your cool...way to go!! :D – BruceWayne Sep 10 '19 at 21:01
• Can this be edited so that it returns only distinct values? – akinuri Mar 23 '20 at 9:12

I feel that a simple index and match function would much simpler.

• The formula will break in case there is another value at the end that satisfies the condition. Long story short, it will have unwanted int values(numbers) along the way. Also, it will be great if you could post the actual code, not captured image.`=IFERROR(INDEX(A2:B5,1,MATCH(\$D\$1,B2:B5,0)),"")` – haku Apr 17 '19 at 10:29