Retrieving data where it meets criteria in another column

[amberdk]

I need to make a list of all values where in Column A that meet certain criteria in Column B. Ultimately so that I can do a Vlookup. For example:




[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]Collections Firm[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Harrison[/TD]
[TD]Firm A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mitchell[/TD]
[TD]Firm B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ward[/TD]
[TD]Firm A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Johnson[/TD]
[TD]Firm C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jenkins[/TD]
[TD]Firm C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kruger[/TD]
[TD]Firm C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Firm B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rogers[/TD]
[TD]Firm A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jaeger[/TD]
[TD]Firm B[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In column C, I need to create a list that would retrieve everything that is with Firm C. So in this case, column C would be filled with Johnson, Jenkins, Kruger.

I have tried a pivot table, but the problem is I need it in columns because I am going to end up doing a vlookup off of this, and it is for a template, so the hope would be to drop a report in this each month and cells populate. When I tried the pivot, the vlookup would have to change every month based on the new data, and I have a huge set of data so it would be tedious to go through it and change it. I have Kutools if this helps.

Hopefully I explained it correctly.

 

[Scott T]

[TABLE="class: grid, width: 373"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Last Name
[/TD]
[TD]Collections Firm
[/TD]
[TD]Column C
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Harrison
[/TD]
[TD]Firm A
[/TD]
[TD]Johnson
[/TD]
[TD][/TD]
[TD]Firm C
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Mitchell
[/TD]
[TD]Firm B
[/TD]
[TD]Jenkins
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Ward
[/TD]
[TD]Firm A
[/TD]
[TD]Kruger
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Johnson
[/TD]
[TD]Firm C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Jenkins
[/TD]
[TD]Firm C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Kruger
[/TD]
[TD]Firm C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Smith
[/TD]
[TD]Firm B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Rogers
[/TD]
[TD]Firm A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Jaeger
[/TD]
[TD]Firm B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In E2 put the firm you are looking up

In C2 and copy down

This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IF(ROWS($C$2:C2)>COUNTIF($B$2:$B$10,$E$2),"",INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=$E$2,ROW($B$2:$B$10)-ROW($B$2)+1),ROWS($C$2:C2))))

 

[Scott T]

In response to your PM the formula posted above will return the item in Column A when Column B equals what is in E2. To look up Firm A just type Firm A into E2. If you will not be changing the firm then you can hardcode it into the formula.

 

[amberdk]

In response to your PM the formula posted above will return the item in Column A when Column B equals what is in E2. To look up Firm A just type Firm A into E2. If you will not be changing the firm then you can hardcode it into the formula.

Ahh, I see whats going on. I cannot see the formula, which explains my confusion. The code box is not allowing me to scroll to find the formula you are giving.

 

[Aladin Akyurek]

[TABLE="class: grid, width: 384"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]Collections Firm[/TD]
[TD]Firm C[/TD]
[/TR]
[TR]
[TD]Harrison[/TD]
[TD]Firm A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Mitchell[/TD]
[TD]Firm B[/TD]
[TD]Name List[/TD]
[/TR]
[TR]
[TD]Ward[/TD]
[TD]Firm A[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD]Johnson[/TD]
[TD]Firm C[/TD]
[TD]Jenkins[/TD]
[/TR]
[TR]
[TD]Jenkins[/TD]
[TD]Firm C[/TD]
[TD]Kruger[/TD]
[/TR]
[TR]
[TD]Kruger[/TD]
[TD]Firm C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Firm B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rogers[/TD]
[TD]Firm A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jaeger[/TD]
[TD]Firm B[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

C1 houses a firm of interest.

In C2 just enter:

=COUNTIFS($B$2:$B$10,C$1)

In C4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(C$4:C4)>C$2,"",INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=C$1,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$4:C4))))

This set up should be a tad faster.