+ Reply to Thread
Results 1 to 4 of 4

Nesting a CSE function in a IFERROR function

  1. #1
    Registered User
    Join Date
    04-08-2020
    Location
    Netherlands
    MS-Off Ver
    excel 365
    Posts
    4

    Nesting a CSE function in a IFERROR function

    So I'm currently performing a task in which I needed the combination of a Index, Match, Exact formula. I got the formula to work and it is linking to another spreadsheet which has to remain in its current layout. (Because the export will be delivered in this exact format again in the future)

    The problem is that the Index, Match, Exact formula is only part of the solution. My previous strategy was to have an IFERROR function cycle through the possible locations of the desired solutions in correct order. But when I try to implement the CSE function which has to be executed by CTRL, Shift, Enter this does not seem to work. (even if I use " { } " to locate the CSE part of the formula.

    Now my question is, is it even possible to nest a CSE formula in a much larger formula containing IFERROR and other excel functions?

    Thank you in advance!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,468

    Re: Nesting a CSE function in a IFERROR function

    A formula is either an Array Formula or it is not. You cannot have part of a formula as an Array Formula.

    When you commit an Array Formula with Ctrl-Shift-Enter (CSE), as opposed to just Enter, it automatically adds the curly brackets. You cannot manually enter them ... well, you can try, but it wouldn't make it an Array Formula.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-08-2020
    Location
    Netherlands
    MS-Off Ver
    excel 365
    Posts
    4

    Re: Nesting a CSE function in a IFERROR function

    I've found a solution and have found the answer to my own question.
    First of all it is not possible to write the { } in a formula to indicate an array function (CSE).
    However the solution is quite obvious... I found out that even if only part of your formula is an array you can stil use the ctrl shift enter comment and the rest of the formula will work just fine.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,468

    Re: Nesting a CSE function in a IFERROR function

    You're welcome. Thanks for the rep.

    Thanks for sharing/confirming the answer. Glad you have your solution



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 07-05-2018, 02:50 PM
  2. Replies: 2
    Last Post: 07-03-2018, 02:26 PM
  3. Nesting and Index and Match function inside an IF function
    By breckleeb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-29-2016, 11:06 AM
  4. Need help nesting an index/match function within a Vlookup function.
    By Christopher135 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2013, 06:16 PM
  5. [SOLVED] MATCH function workaround - nesting ADDRESS function?
    By BishBosh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-18-2013, 03:45 AM
  6. [SOLVED] IFERROR function with Hour function Fail
    By Kram222 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2012, 12:25 AM
  7. New Function in Excel 2007 - IFERROR Function
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-15-2007, 05:09 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