INDEX SMALL with conditional IF and OR

**cedarhill** · 02-01-2010, 02:32 PM

I'm creating a report that pulls certain record entries from another worksheet (Activity) in the same book. Originally, it only pulled one type of activity... "SO". Now I need it to pull "SO", "CC" and "BT" type activities into the same report.

Currently my formula looks like this and works fine for only looking for the "SO" type:

=IF(ROW($B3)-ROW($B$3)+1>$M$1,"",INDEX(Activity!$B$1:$B$2000,SMALL(IF(Activity!E$3:E$2000="SO",ROW($A$3:$A$2000),99999),ROW($B3)-ROW($B$3)+1)))

However, when I tried adding OR conditions to include the "CC" and "BT" types...well, it didn't work.

I'm using Excel 2003...and I need help adding in the conditional logic for the two new types to also be selected.

**NBVC** · 02-01-2010, 02:36 PM

Try:

=IF(ROW($B3)-ROW($B$3)+1>$M$1,"",INDEX(Activity!$B$1:$B$2000,SMALL(IF(Activity!E$3:E$2000={"SO","CC","BT"},ROW($A$3:$A$2000),99999),ROW($B3)-ROW($B$3)+1)))

**cedarhill** · 02-01-2010, 02:43 PM

Well, that worked perfectly. The solution was simple and elegant...you wouldn't believe the gyrations I tried. Thank you. I look forward to marking this "solved".