Here's one play using non-array formulas which might also deliver this ..

A sample construct is available at:
http://www.savefile.com/files/9870225
Multiple search & auto-extract rows from 3 sheets based on key charge code
col.xls

Assume source data is in Sheets 1 to 3 (identically structured)
data within cols A to J, from row6 down to say,
a max expected row15 (Col headers in row5)
The key charge codes are assumed in col A

In Sheet1,

Put in K6:
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A $1:$A$3,A6))*(Summ!$A$1:$A$3<""))0,ROW(A1),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet2

Put in K6:
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A $1:$A$3,A6))*(Summ!$A$1:$A$3<""))0,ROW(A1)+MAX(S heet1!K:K),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet3

Put in K6:
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A $1:$A$3,A6))*(Summ!$A$1:$A$3<""))0,ROW(A1)+MAX(S heet2!K:K),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In a new sheet: Summ,

Entry of the charge code(s) to search will be made within A1:A3 (can be in
any order). To facilitate search input(s), format A1:A3 as text (Format
Cells Text)
Input 2 charge codes into A1:A2, say: 1-90, 1-91

Place the same col labels in A5:J5

Then put in A6:

=IF(ISERROR(SMALL(Sheet1!$K:$K,ROW(A1))),
IF(ISERROR(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K))),
IF(ISERROR(SMALL(Sheet3!$K:$K,ROW(A1)-(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)))),"",
INDEX(Sheet3!A:A,MATCH(SMALL(Sheet3!$K:$K,ROW(A1)-(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K))),Sheet3! $K:$K,0))),
INDEX(Sheet2!A:A,MATCH(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K)),Sheet2!$K:$K,0))),
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)) ,Sheet1!$K:$K,0)))

Copy A6 across to J6, fill down to cover the *total* expected range in the 3
sheets, ie to J35 (in this example, the expected max data range is: 10 rows
per sheet x 3 sheets = 30 rows)

The auto-extracted results from Sheets 1 to 3 will be returned within
A6:J35, all neatly bunched at the top. Extracted lines will be listed in the
order: Lines from Sheet1, then those from Sheet2, then those from Sheet3.
Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aine" wrote:
Hi All,

Sorry, this may seem like an elementary question but....

First of all I have three seperate worksheets containing data within my
workbook.

I want to return all rows from each of those worksheets that matches a
certain criteria to another worksheet, without having to manipulate the
data.

e.g.
I have charge codes beginning in 1-90XX, 1-91xx, etc...
These appear multiple times in the worksheets that contain data
I want to search sheets 1 - 3 to see if any rows contain them & if they
do, I want all these complete rows to appear in another worksheet
within that same workbook.



VLOOKUP function will not work for me as the charge code is in column O
& I can only get it to return data within that row for column O onward.

Also, I am unsure if you can get VLOOKUP to check for multiple
conditions

* What function should I be using?
* Can you use VLOOKUP in a nested statement to search for the various
conditions(charge codes)?
* Can VLOOKUP return a whole row, without having to specify the column?
* Can VLOOKUP return data previous to the column where the criteria of
the search is met?

* Finally: Should I try to use Macros & if so, can someone point me in
the right direction???


Thanks,

Aine