Multiple conditional list

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(SMALL(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
>
>

> 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
---