Searching a column for a value and returning a list of corresponding values

Hi there,

Here is an example to demonstrate what I would like to do:

Say I have 2 columns -

column 1----column 2
---a------------1----
---b------------2----
---b------------3----
---b------------4----
---a------------5----
---b------------6----
---a------------7----
---b------------8----

Is there a way I can make a new column that searches for all the a's and returns the corresponding column 2 values to the top of the new column and working down. So the final column would look like this:

column 3
----1----
----5----
----7----

Thanks!

Try this.

D1: a
D2: =COUNTIF(A:A,D1)
F1: =IF(ROWS($A$1:$A1)>$D$2, "", INDEX($B$1:$B$100, SMALL(IF($A$1:$A$100=$D$1, ROW($A$1:$A$100), ""), ROWS($A$1:$A1))))
...confirmed by pressing Ctrl-Shift-Enter to activate the array formula

The first answer of 1 should appear.

Copy F1 and paste in F2:F20.

Not simply, no. Putting all the values on a single sheet would let you continue to use this array formula approach.

If you move to a VBA programming approach, then you could search as many sheets as you wish to compile a summary list on a specific sheet.