Search more than one result

[mauddib]

How can you search more than one result from a list without using INDEX
I have used Index before like so:

INDEX($D$2:$I$1000,SMALL(IF($I$2:$I$1000>$B$6,ROW($I$2:$I$1000)-ROW($I$2)+1,ROW($I$1000)+1),-9+ROW()),6))

If I put this formula into row 10 and drag it down it will return al
the numbers greater than whats in B6. However I cant get it to work i
there is MORE than one condition to fulfil.

This is what I want to do. I have a range of information from D2 to sa
N1000. This is pulled out of a database. Coloumn H is a date fo
example. I want to return all the entries in this list where EITHER:

A) The value in coloumn K which is =YEAR(H2) is less than YEAR(NOW()).
B) The value in coloumn K which is =YEAR(H2) is EQUAL TO YEAR(NOW()
AND The value in column L which is WEEKNUM(H2) is less tha
WEEKNUM(NOW())

(**Obviously H2 is just the first feild and becomes H3, H4 and s
on**)

However if I try this in index it wont work (using OR(condition a
AND(conditionb1, condition b2)))

And suggestions? Is there a way to use VLOOKUP to do this

 

[Tom Ogilvy]

addition would be used for OR and multiplication would be use for AND

if( (condition1 * condition2)+(condition3*1)>0,True,False)

equvalent to
OR(And(condition1,condition2),condition3)

but works in an array formula.