Forum Discussion

Hussein_Mohamed's avatar
Hussein_Mohamed
Brass Contributor
Oct 25, 2023
Solved

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('Unde...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 25, 2023

    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

Hussein_Mohamed's avatar
Hussein_Mohamed
Brass Contributor
Oct 25, 2023

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 25, 2023

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

  • Hussein_Mohamed's avatar
    Hussein_Mohamed
    Brass Contributor
    Oct 25, 2023
    Hartfull thank you for your support, it works well