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

--
Message posted from http://www.ExcelForum.com

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.

--
Regards,
Tom Ogilvy

"mauddib " wrote in message
...
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 all
the numbers greater than whats in B6. However I cant get it to work if
there is MORE than one condition to fulfil.

This is what I want to do. I have a range of information from D2 to say
N1000. This is pulled out of a database. Coloumn H is a date for
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 than
WEEKNUM(NOW())

(**Obviously H2 is just the first feild and becomes H3, H4 and so
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?


---
Message posted from http://www.ExcelForum.com/