Forum Discussion
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...
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
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"))
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.
Thanks
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
