adding AND, OR to excisting INDEX formula
 

Hi, in case I use the following formula:

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9);0))

Is it possible to find the match with 'Homer OR Janssen', instead of just
'Homer'?

And also, can I ask for an extra criteria from another column using AND?

So what I want is a match with the row which contains:
'L-1234' in column B;
AND 'Homer' OR 'Janssen' OR 'Smith' in column C;
AND 'plastic' in column E.

I really hope somehow this is possible.

Thanks a lot in advance.

Marieke

adding AND, OR to excisting INDEX formula
 

Try:


Code:
--------------------
=INDEX(A1:A9,MATCH(1,("L-1234"=B1:B9)*(ISNUMBER(MATCH(C1:C9,{"Homer","Janss en","Smith"},0)))*(E1:E9="plastic"),0))
--------------------


confirmed with CTRL+SHIFT+ENTER

or


Code:
--------------------
=INDEX(A1:A9;MATCH(1;INDEX(("L-1234"=B1:B9)*(ISNUMBER(MATCH(C1:C9;{"Homer";"Janss en";"Smith"};0)))*(E1:E9="plastic");0);0))
--------------------


normally entered with just ENTER


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109827