+ Reply to Thread
Results 1 to 3 of 3

Equivalent to java break(); function in basic excel formulas?

  1. #1
    Registered User
    Join Date
    05-17-2019
    Location
    Madison
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    13

    Equivalent to java break(); function in basic excel formulas?

    Hello,

    I've been looking around and I'm not sure if it's possible, but I would like to know if there's an equivalent to Java's break(); function in basic excel formulas. For those who don't know Java, the break() essentially, I'm looking for a way to exit a formula early under certain conditions, so that the cell's value would evaluate to be blank.

    This is the formula I'm currently using:
    =IF(ISNA((INDEX('Master New Hire List.xlsx]Sheet2'!$L:$L,MATCH(IF(("*"&K7&"*")="**",NA(),"*"&K7&"*"),'Master New Hire List.xlsx]Sheet2'!$AQ:$AQ,0)))), NA(),"✓")

    K7 is a column which contains potential new email addresses. Right now, by design it is throwing a N/A if K7 is a blank cell. 'Master New Hire List.xlsx]Sheet2'!$L:$L returns a name, and 'Master New Hire List.xlsx]Sheet2'!$AQ:$AQ is searching through another database of known email addresses.

    The reason I am looking for this functionality at all is that I need some way to differentiate between errors due to a nonmatch, and errors due to there being no data in K7. If there is no break(); function in basic excel formulas, I would also appreciate any advice on another way I could go about doing this.

    Thanks so much!

    BTW: sorry if this would better fit in Formula's and Functions--I wasn't sure.
    Last edited by Sprong; 05-22-2019 at 11:00 PM. Reason: question answered

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Equivalent to java break(); function in basic excel formulas?

    There's no equivalent in Excel, but what you could do is simply arrange your formula so the first thing it does is check K7 and if it's empty return a blank.

    =IF(K7="", "", IF(ISNA((INDEX('Master New Hire List.xlsx]Sheet2'!$L:$L,MATCH(IF(("*"&K7&"*")="**",NA(),"*"&K7&"*"),'Master New Hire List.xlsx]Sheet2'!$AQ:$AQ,0)))), NA(),"✓"))
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-17-2019
    Location
    Madison
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    13

    Re: Equivalent to java break(); function in basic excel formulas?

    Wow I was overthinking that. Thanks so much!

+ 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. [SOLVED] Excel/VBA equivalent to DateAdd() function
    By Bezzie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-03-2015, 01:53 AM
  2. What is the equivalent of continue and break commands?
    By pericopericone in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2013, 10:32 PM
  3. Access Equivalent of this Excel VBA Function?
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2012, 06:34 AM
  4. Replies: 3
    Last Post: 02-03-2010, 01:32 PM
  5. List of Basic Excel Worksheet Formulas
    By vinitSF in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-24-2009, 11:04 PM
  6. Need LEN(TRIM()) equivalent one excel function
    By suman.biswas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2008, 04:02 AM
  7. Equivalent of countifs function for Excel 2003?
    By oneyejack in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-29-2007, 12:51 PM

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