• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is it possible to shorten this formula ?

smaoketown2021

New Member
Anyway I can shorten below formula ?
I tried defining SEARCH as XE and ISERROR as XR using LET function but that doesn't seem to work.
This is only part of the actual formula. The original version I intend to use has many more IF statements. I was hoping If I can get help to shorten this then I can use the same logic with the rest.


=LET(XO,Table1[Pricing],IF($D$1="","No match found",IF(AND(NameSelected="",$D$1="unassigned",$C$11=""),IFERROR(SORT(FILTER(FILTER(Table1[[Title]:[Launch Date]],((Table1[Status]="Expected")*(ISNUMBER(SEARCH("air",Table1[Mode])))*(ISERROR(SEARCH('Contact List'!$B$2,XO)))*(ISERROR(SEARCH('Contact List'!$B$3,XO)))*(ISERROR(SEARCH('Contact List'!$B$4,XO)))*(ISERROR(SEARCH('Contact List'!$B$5,XO)))*(ISERROR(SEARCH('Contact List'!$B$6,XO)))*(ISERROR(SEARCH('Contact List'!$B$7,XO)))*(ISERROR(SEARCH('Contact List'!$B$8,XO)))*(ISERROR(SEARCH('Contact List'!$B$9,XO)))*(ISERROR(SEARCH('Contact List'!$C$2,XO)))*(ISERROR(SEARCH('Contact List'!$C$3,XO)))*(ISERROR(SEARCH('Contact List'!$C$4,XO)))*(ISERROR(SEARCH('Contact List'!$C$5,XO)))*(ISERROR(SEARCH('Contact List'!$C$6,XO)))*(ISERROR(SEARCH('Contact List'!$D$2,XO)))*(ISERROR(SEARCH('Contact List'!$D$3,XO)))*(ISERROR(SEARCH('Contact List'!$D$4,XO)))*(ISERROR(SEARCH('Contact List'!$D$5,XO)))*(ISERROR(SEARCH('Contact List'!$D$6,XO)))*(ISERROR(SEARCH('Contact List'!$D$7,XO)))*(ISERROR(SEARCH('Contact List'!$E$2,XO)))*(ISERROR(SEARCH('Contact List'!$E$3,XO)))*(ISERROR(SEARCH('Contact List'!$E$4,XO)))*(ISERROR(SEARCH('Contact List'!$E$5,XO)))*(ISERROR(SEARCH('Contact List'!$E$6,XO)))*(ISERROR(SEARCH('Contact List'!$E$7,XO)))*(ISERROR(SEARCH('Contact List'!$E$8,XO)))*(ISERROR(SEARCH('Contact List'!$E$9,XO)))*(ISERROR(SEARCH('Contact List'!$E$10,XO)))*(ISERROR(SEARCH('Contact List'!$F$2,XO))))+((Table1[Status]="Expected")*((ISNUMBER(SEARCH("LCL",Table1[Mode])))
+(ISNUMBER(SEARCH("FCL",Table1[Mode]))))*(ISERROR(SEARCH('Contact List'!$G$2,XO)))*(ISERROR(SEARCH('Contact List'!$G$3,XO)))*(ISERROR(SEARCH('Contact List'!$G$4,XO)))*(ISERROR(SEARCH('Contact List'!$G$5,XO)))*(ISERROR(SEARCH('Contact List'!$G$6,XO)))*(ISERROR(SEARCH('Contact List'!$G$7,XO)))*(ISERROR(SEARCH('Contact List'!$G$8,XO)))*(ISERROR(SEARCH('Contact List'!$G$9,XO)))*(ISERROR(SEARCH('Contact List'!$G$10,XO)))*(ISERROR(SEARCH('Contact List'!$G$11,XO)))*(ISERROR(SEARCH('Contact List'!$G$12,XO)))*(ISERROR(SEARCH('Contact List'!$H$2,XO)))*(ISERROR(SEARCH('Contact List'!$H$3,XO)))*(ISERROR(SEARCH('Contact List'!$H$4,XO)))*(ISERROR(SEARCH('Contact List'!$H$5,XO)))*(ISERROR(SEARCH('Contact List'!$H$6,XO)))*(ISERROR(SEARCH('Contact List'!$H$7,XO)))*(ISERROR(SEARCH('Contact List'!$I$2,XO)))*(ISERROR(SEARCH('Contact List'!$I$3,XO)))*(ISERROR(SEARCH('Contact List'!$I$4,XO)))*(ISERROR(SEARCH('Contact List'!$I$5,XO)))),"No match found"),{1,0,0,0,1,0,0,1,0,0,0,0,0,1}),4,1),"No match found"))))
 
Almost impossible to say without knowing what's in Table1, what the absolute references contain, what's in the Name(s).
Best attach a sample workbook - and perhaps what the formula's supposed to be doing (in words).
 
Back
Top