6

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.

Improve this question
2
  • 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, 2017 at 16:06
  • 1
    You can just use a filter -- either the simple or advanced filter can accomplish that.
    – Ron Rosenfeld
    Jan 23, 2017 at 1:44

5 Answers 5

Reset to default
8

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.
Improve this answer
1
  • 1
    Worth noting FILTER function is only available to 365 subscribers not regular Excel users.
    – John U
    Jul 24, 2023 at 10:49
5

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

Improve this answer
7
  • 3
    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, 2017 at 17:25
  • @ScottCraner - that's really nifty, thanks for that!!
    – BruceWayne
    Jan 19, 2017 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, 2019 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, 2019 at 21:01
  • 1
    Can this be edited so that it returns only distinct values?
    – akinuri
    Mar 23, 2020 at 9:12
1

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

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

Excel print showing the formula and results

Improve this answer
1
  • 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)),"")
    – h2ku
    Apr 17, 2019 at 10:29
0

You can use this formula :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:B)/(B:B=4),ROW(1:1))),"")
Improve this answer
1
  • 1
    As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Jan 15, 2022 at 14:40
0

If you don't want the results to spill over in to other cells, you can use TEXTJOIN and a condition, which will create an in-the-cell listing of what you're looking for.

=TEXTJOIN(", ", TRUE, UNIQUE(IF(D1=B:B,A:A,"")))

Where D1 would be the value of "4" or whatever you're looking for.

Which would give you a result of:

Adam, Dave, Ryan

Improve this answer
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Mar 12 at 21:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .