MATCH/INDEX IF question

Hi all,

I'll keep it simple...

{=INDEX($A$1:$N$317,SMALL(IF($E$1:$E$317="BURSTING",ROW($E$1:$E$317)),ROW(1:1)),1)}

This brings me my first instance of a row containing "BURSTING" from column E.

How do I manipulate the formula to search for THREE things? For example, I need to retrieve the first instance of a row containing EITHER "BURSTING", "FOLDING" OR "INSERTING" from column E. And basically each line underneath will bring me the second instance, the third instance etc. by using the (2:2) (3:3) (4:4) and so on.

Hope you can help, thanks.

Lee.

U cannot use OR function within array formulae. It's common rule. You should use "+" (plus sign) instead. When you use "+" that's mean Either... Or... construction. But when multiply this would mean And construction..
Try
=INDEX($A$1:$N$317,SMALL(IF(($E$1:$E$317="BURSTING")+($E$1:$E$317="FOLDING")+($E$1:$E$317="INSERTING"),ROW($E$1:$E$317)),ROW( 1:1)),1)

Originally Posted by contiminated

U cannot use OR function within array formulae. It's common rule. You should use "+" (plus sign) instead.

Whilst the above is true you may find that if you have lots of mutually exclusive conditions it is simpler to use an ISNUMBER MATCH test (shorter syntax and easier to adapt the criteria)

Please Login or Register  to view this content.

That's the trick Donkey... that's the trick...!!!! Really haven't seen this kind of syntax before...