formula assist

Brass Contributor

Dear Gents,

i have a formula and need to add a condition if i need to get data if meet the condition for both parts of formula like(if range()="Contract",get the data matched


VSTACK(FILTER('Under Collection'!B:B,ISNUMBER(FIND(Aging!$B$2,'Under Collection'!C:C))),FILTER(AR!B:B,ISNUMBER(FIND(Aging!$B$2,AR!C:C))))


N.B: the database in two sheets

Thanks in advance

5 Replies
To add additional conditions in a FILTER just multiply them (conditional 1)*(conditional 2) so
VSTACK(FILTER('Under Collection'!B:B,ISNUMBER(FIND(Aging!$B$2,'Under Collection'!C:C))*(range2="Contract")),FILTER(AR!B:B,ISNUMBER(FIND(Aging!$B$2,AR!C:C))*(range2="Contract")))
That said I recommend you convert the data on 'Under Collection' and 'AR' into Tables and then you can reference that table data instead of the entire column of the sheet. you can also combine them and then FILTER instead:
FILTER(VSTACK(Under_Collection_Table[ColBname],AR_Table[ColBname]),ISNUMBER(FIND(Aging!$B$2,VSTACK(Under_Collection_Table[ColCname], AR_Table[ColCname])))*(range2="Contract"))


thank you for your reply, i am unfortuntly fail to add the criteria in the formula, i have attached the original sheet culumn B is the range in both sheets.

if you please reform the formula in the aging sheet to get the data in a simple form.



best response confirmed by Hussein_Mohamed (Brass Contributor)


That could be

=FILTER( 'Under Collection'!C:C,
    ISNUMBER(SEARCH(Aging!$B$2,'Under Collection'!C:C))*
    ISNUMBER(SEARCH($C$2,'Under Collection'!B:B))

- AND(), OR() don't work with the FILTER(). Use * and + instead

- FIND() is case sensitive, I guess in your case SEARCH() is more suitable

Hartfull thank you for your support, it works well

@Hussein_Mohamed , you are welcome