Results 1 to 9 of 9

FILTER FUNCTION nested if multi, better/simpler expression?

Threaded View

  1. #1
    Registered User
    Join Date
    11-01-2021
    Location
    Hull, England
    MS-Off Ver
    2021
    Posts
    38

    Talking FILTER FUNCTION nested if multi, better/simpler expression?

    Hello

    XL2021 Database with SEARCH feature in 1st tab, and DATA in 2nd Tab, it is fully working, see attachment.
    And also since recently purchased XL21, wow what a major time saver Excel2021 is, brilliant features close to SQL could have done with this 20 years ago as concept seems simple enough, but anyway in XL21 it is much concise formulas edit and file size, something similar to achieve in Excel 2003 would have been lengthy edit formulas over many tabs, and who knows how many hours/days/weeks/months/years!

    Was wondering if Filter Function expression has simpler alternative, or better way? Even though Filter Function is either AND or OR, anyway!

    Currently 5 seperate formulas via nested if (Col D-A, or Col C-A, or Col B-A, Col A only or Blank Combo Boxes), as clearly seen in screenshot:
    Screenshot (483).png

    Think the 5 seperate formulas (Col A, Col B, Col C, Col D, Blank) as constructed for SEARCH AZ RESULTS A12 spill array is better then 1 nested if as easier to follow/edit at later stage and not get lost somewhere between, look at the 1nest alternative!:

    =UNIQUE(IF($D$2<>"",IF('SEARCH AZ'!A2<>0,(SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)*(IF('SEARCH AZ'!C2<>0,(DATA!C:C='SEARCH AZ'!C2)*(IF('SEARCH AZ'!D2<>"",(DATA!D:D='SEARCH AZ'!D2),"HELLO"))))))),5,1))),IF($C$2<>"",IF('SEARCH AZ'!A2<>0,(SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)*(IF('SEARCH AZ'!C2<>0,(DATA!C:C='SEARCH AZ'!C2)))))),4,1))),IF($B$2<>"",IF('SEARCH AZ'!A2<>0,(SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)))),3,1))),IF($A$2<>"",IF('SEARCH AZ'!A2="","",SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)),2,1)),IF('SEARCH AZ'!A2<>"","",SORT(FILTER(DATA!A:F,(DATA!A:A<>"MAKE")),1,1)))))))


    Cheers Stephan
    Last edited by StephanRS; 03-19-2023 at 05:46 PM. Reason: FILE

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multi IF with filter function
    By mazan2010 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-10-2021, 10:36 AM
  2. Simpler formula to handle nested OFFSET function
    By lamlam28 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-18-2019, 03:58 AM
  3. Simpler way than 10+ nested IF statements?
    By jobinv123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-14-2015, 11:40 PM
  4. Replies: 2
    Last Post: 05-22-2013, 05:39 PM
  5. A simpler way to do nested vlookups?
    By bsoper in forum Excel General
    Replies: 4
    Last Post: 01-06-2011, 03:43 PM
  6. Nested IF function with multi AND,OR
    By MAHMUZ in forum Excel General
    Replies: 4
    Last Post: 04-29-2010, 11:01 AM
  7. Multi Nested function formula Error
    By Cullihall in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2008, 10:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1