ExcelBanter - VLookup needed to search one value and return multipe results

Another much simpler & faster alternative, imo, is to use just one helper col
to specify the requisite criteria, then you can extract all the multiple line
results which will be neatly packed at the top

Here's an example using my response to a similar query in the recent past
.. I have a worksheet with 5 columns and 600 rows. I need to copy all the
rows into a new worksheet that have a specific value in column 3. What
formula do I use for this?

Here's a formulas set-up to accomplish it ..

Assume source data in Sheet1, cols A to E,
data from row2 down, key col = col C

In another sheet
Assume the specific value for col C will be entered in A2
In B2: =IF(A$2="","",IF(Sheet1!C2=A$2,ROW(),""))
Leave B1 empty

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(Sheet1!A:A,SM ALL($B:$B,ROWS($1:1))))
Copy C2 across by 5 cols to G2. Select B2:G2, fill down to cover the max
expected extent of source data in Sheet1, say down to G600. Minimize/hide
away col B. Cols C to G will return the required results from Sheet1
(dependent on the input in A2), with all lines neatly packed at the top.

Success? High five it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---