SOLVED

# formula assist

Brass Contributor

# formula assist

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

5 Replies

# Re: formula assist

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

# Re: formula assist

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

best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: formula assist

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

# Re: formula assist

Hartfull thank you for your support, it works well

# Re: formula assist

@Hussein_Mohamed , you are welcome

1 best response

Accepted Solutions
best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: formula assist

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