Return row numbers with mutiple criteria

[foreveryone]

Hi. I'm trying to build a few tables using named ranges and array formulae but have hit a snag, hoping someone can help.

My strategy is to return the row numbers in a data table that match certain conditions and then use these in an INDEX function to return the data. My formula for the row numbers is:

{=SMALL(IF(Status="Follow-up",ROW(Status)),ROW())}

which I'll copy down a column. Status is a named range in this example.

What I'd like to do is add an OR condition to the IF so I can return rows where Status="Follow-up" or "Something else". Just thinking as I type, I guess I can add the conditions together somehow? I guess all I'm looking for is a single column of 0s and 1s to pass to the ROW function..

Thanks

 

[foreveryone]

Actually figuring this out now, perhaps a bit too soon in posting..

This works for the row numbers:

{=SMALL(IF(Status="Follow-up",ROW(Status))+IF(Status="Something else",ROW(Status)),ROW())}

Just need to find a way of coercing the 0s to errors as otherwise the SMALL function returns them. Guess I can switch to LARGE?

Sorry to answer my own questions - any alternatives or suggestions still gratefully received.

 

[Saba Sabaratnam]

Hi Foreveryone,

Use this formula. Note that only one if is used. It creates row number if A or B is true. On other hand, if both are false, it creates 'FALSE' (not zero). So the formula should work.

=SMALL(IF(($A$1:$A$8="A")+($A$1:$A$8="B"),ROW($A$1:$A$8)),ROW())


Please let me know you go.

Kind regards

Saba

 

[foreveryone]

That's neater - thanks, works well

 

[foreveryone]

PS assuming I can replace the + with * to create AND logic?

 

[Saba Sabaratnam]

Yes you are right.