3

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.

  • 2
    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
  • 1
    You can just use a filter -- either the simple or advanced filter can accomplish that. – Ron Rosenfeld Jan 23 '17 at 1:44
4

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.

enter image description here

  • 1
    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
  • 1
    @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
  • 1
    @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
0

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

https://res.cloudinary.com/dlkdmqcop/image/upload/v1552332895/excel_pic_wq2o0v.png

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.