Search more than one result
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 |
Search more than one result
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/ |
All times are GMT +1. The time now is 08:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com