Hello,

I am trying to auto generate a list on a worksheet based on MULTIPLE
conditions in
another i.e.

Worksheet 1 Contains the list below,

tree green america-north sequoia
tree green america-south cedar
tree green america-south tall cedar
tree red asia asiatictree

I want a list to be generated in worksheet2, based on multiple
criterias:
col1: tree
col2: green
col3: left(col3;7)=america

Result would look like:
sequoia row
cedar row
tall cedar row

I need to use a function to make it, not a macro.

I have read several post on using index & small, but I cannot make this
on working...

Thanks a lot for your help

One play ..

Assuming source table in Sheet1, cols A to C, data from row2 down

In Sheet2,

Let's reserve C1:C3 for input of criteria for cols A to C
Input in C1:C3 : tree, green, america

Then

Put in A2:
=IF(ROW(A1)COUNT(B:B),"",INDEX(Sheet1!D:D,MATCH(S MALL(B:B,ROW(A1)),B:B,0)))

Put in B2:
=IF(COUNTBLANK($C$1:$C$3)0,"",IF(AND(Sheet1!A2=$C $1,Sheet1!B2=$C$2,ISNUMBER(SEARCH($C$3,Sheet1!C2)) ),ROW(),""))
(Leave B1 empty)

Select A2:B2, copy down to say, B100,
cover the max expected extent of data in Sheet1

Col A will return the required results
(Hide away the criteria col B, if necess)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"CEL" wrote:
Hello,

I am trying to auto generate a list on a worksheet based on MULTIPLE
conditions in
another i.e.

Worksheet 1 Contains the list below,

tree green america-north sequoia
tree green america-south cedar
tree green america-south tall cedar
tree red asia asiatictree

I want a list to be generated in worksheet2, based on multiple
criterias:
col1: tree
col2: green
col3: left(col3;7)=america

Result would look like:
sequoia row
cedar row
tall cedar row

I need to use a function to make it, not a macro.

I have read several post on using index & small, but I cannot make this
on working...

Thanks a lot for your help

select a number of rows in a column that is the max number of possible
matches, and in the formula bar enter

=IF(ISERROR(SMALL(IF(($A$1:$A$20="tree")*($B$1:$B$ 20="green")*(LEFT($C$1:$C$
20,7)="america"),ROW($A1:$A20),""),ROW($A1:$A20))) ,"",
INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20="tree")*($B$1: $B$20="green")*(LEFT($C$1:
$C$20,7)="america"),ROW($A1:$A20),""),ROW($A1:$A20 ))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy this block of cells across 4 columns.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CEL" wrote in message
oups.com...
Hello,

I am trying to auto generate a list on a worksheet based on MULTIPLE
conditions in
another i.e.

Worksheet 1 Contains the list below,

tree green america-north sequoia
tree green america-south cedar
tree green america-south tall cedar
tree red asia asiatictree

I want a list to be generated in worksheet2, based on multiple
criterias:
col1: tree
col2: green
col3: left(col3;7)=america

Result would look like:
sequoia row
cedar row
tall cedar row

I need to use a function to make it, not a macro.

I have read several post on using index & small, but I cannot make this
on working...

Thanks a lot for your help

Col A will return the required results

Results in col A will all be neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---