index/match 3 possible forth criteria

[KDS14589]

Hello All

I had problem adding a fourth criteria, but with the help from a Mr Excel moderator In cell Avoid!BL18 I came up with:

=IFERROR(INDEX(statement_name,AGGREGATE(15,6,(ROW(statement_name)-ROW(Statement!$AA$10)+1)/(statement_Bal_this_month>$BM$15)/(statement_Bal_this_month<=$BM$16)/(statement_min_due>0)/(statement_name=Avoid_name_1),ROWS(Avoid!$BL$18:BL18))),"")

it's the (statement_name=Avoid_name) part.



Now I've added a wrinkle by now wishing to add two more names to that criterion.



This is my last attempt:

=IFERROR(INDEX(statement_name,AGGREGATE(15,6,(ROW(statement_name)-ROW(Statement!$AA$10)+1)/(statement_Bal_this_month>$BM$15)/(statement_Bal_this_month<$BM$16)/(statement_min_due>0)/(OR(statement_name=avoid_name1,statement_name=avoid_name2,statement_name=avoid_name3)),ROWS(Avoid!$BL$18:BL18))),"")

But now it lists all the names in the list and not just the three. Hopefully this is possible, but if not or this is to involved, I'll just stick with the original.


Help would be nice.

 

[jasonb75]

Looking at the sheet, I think that my logic was wrong. Try changing >0 to =0 and see if that gives the correct results.

One thing with the layout that gives me cause for concern. If any of the named ranges used in the formula contain merged cells then this type of formula is not going to work well.

 

[KDS14589]

on the change of the >0 to =0 did not help, now I get full list again
the thing about merged cells I'll check, but it'll be awhile because I got to leave home for several days, death in family
after I get back and check I'll let you know
THANKS

 

[jasonb75]

I'm sorry to hear that.

I'll keep your question on my watch list so that I get notified when you return to it.