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:

Last Name	Collections Firm	Column C
Harrison	Firm A
Mitchell	Firm B
Ward	Firm A
Johnson	Firm C
Jenkins	Firm C
Kruger	Firm C
Smith	Firm B
Rogers	Firm A
Jaeger	Firm B

<tbody>
</tbody>

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]

	A	B	C	D	E
1	Last Name	Collections Firm	Column C
2	Harrison	Firm A	Johnson		Firm C
3	Mitchell	Firm B	Jenkins
4	Ward	Firm A	Kruger
5	Johnson	Firm C
6	Jenkins	Firm C
7	Kruger	Firm C
8	Smith	Firm B
9	Rogers	Firm A
10	Jaeger	Firm B

<tbody>
</tbody>

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]

Last Name	Collections Firm	Firm C
Harrison	Firm A	3
Mitchell	Firm B	Name List
Ward	Firm A	Johnson
Johnson	Firm C	Jenkins
Jenkins	Firm C	Kruger
Kruger	Firm C
Smith	Firm B
Rogers	Firm A
Jaeger	Firm B

<tbody>
</tbody>

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.