formula assist

[Hussein_Mohamed]

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

[#2]

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"))

[#3]

@mtarler 

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.

Thanks

[#4 · Accepted solution]

@Hussein_Mohamed 

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

[#5]

Hartfull thank you for your support, it works well

[#6]

@Hussein_Mohamed , you are welcome