+ Reply to Thread
Results 1 to 6 of 6

Reached the limit of MATCH?

  1. 06-01-2016, 11:01 AM #1
    Exequiel3k
    Exequiel3k is offline
    Forum Contributor
    Join Date
    05-17-2016
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    115

    Reached the limit of MATCH?

    Hey,

    I was duplicating this formula

    =IFERROR(INDEX(Z542:AW542;MATCH(0;Z542:AW542;0)-1)*(1-$B$11);IFERROR(IF(MATCH("0.1";Z542:AW542;0)>0;(D541);1);IFERROR(IF(MATCH("0.2";Z542:AW542;0)>0;(D540);1);IFERROR(IF(MATCH("0.3";Z542:AW542;0)>0;(D539);1);IFERROR(IF(MATCH("0.4";Z542:AW542;0)>0;(D538);1);IFERROR(IF(MATCH("0.5";Z542:AW542;0)>0;(D537);1);""))))))

    and I tried to add another "MATCH"

    IFERROR(IF(MATCH("0.6";Z542:AW542;0)>0;(D536);1)

    but it gave me an error indicating that I had reached the limit of parameters for the excel file.

    Is there a way to add more "MATCH" by simplyfing/reformulating the rule??
    Thx!!!
    Reply With QuoteRegister To Reply
  2. 06-01-2016, 11:16 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,317

    Re: Reached the limit of MATCH?

    Try

    =IFERROR(INDEX(Z542:AW542,MATCH(0,Z542:AW542,0)-1)*(1-$B11),IFERROR(IF(MATCH(0.1,Z542:AW542,0)>0,D541,1),IFERROR(IF(MATCH(0.2,Z542:AW542,0)>0,D540,1),IFERROR(IF(MATCH(0.3,Z542:AW542,0)>0,D539,1),IFERROR(IF(MATCH(0.4,Z542:AW542,0)>0,D538,1),IFERROR(IF(MATCH(0.5,Z542:AW542,0)>0,D537,1),IFERROR(IF(MATCH(0.6,Z542:AW542,0)>0,D536,1),"")))))))

    removed "" round your match values as I assumed you are testing a number rather than text string.

    Replace commas "," with semi-colons ";"
    Last edited by JohnTopley; 06-01-2016 at 11:28 AM.
    Reply With QuoteRegister To Reply
  3. 06-01-2016, 11:25 AM #3
    jason.b75
    jason.b75 is offline
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Reached the limit of MATCH?

    Try

    =IFERROR(INDEX(Z542:AW542;MATCH(0;Z542:AW542;0)-1)*(1-$B$11);IFERROR(IF(MATCH("0.1";Z542:AW542;0)>0;(D541);1);IFERROR(IF(MATCH("0.2";Z542:AW542;0)>0;(D540);1);IFERROR(IF(MATCH("0.3";Z542:AW542;0)>0;(D539);1);IFERROR(IF(MATCH("0.4";Z542:AW542;0)>0;(D538);1);IFERROR(IF(MATCH("0.5";Z542:AW542;0)>0;(D537);1);IFERROR(IF(MATCH("0.6";Z542:AW542;0)>0;(D536);1);"")))))))

    If you have the file saved as .xls then you might be exceeding the nesting level for the file type, in which case, you will need to save it as .xlsx or .xlsm, then close, and reopen the new version of the file before entering the formula.
    Reply With QuoteRegister To Reply
  4. 06-01-2016, 11:30 AM #4
    TMS
    TMS is online now
    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
    44,552

    Re: Reached the limit of MATCH?

    I get no complaints about either of these:

    =IFERROR(INDEX(Z542:AW542, MATCH(0,Z542:AW542,0)-1)*(1-$B$11),
    IFERROR(IF(MATCH(0.1,Z542:AW542,0)>0,(D541),1),
    IFERROR(IF(MATCH(0.2,Z542:AW542,0)>0,(D540),1),
    IFERROR(IF(MATCH(0.3,Z542:AW542,0)>0,(D539),1),
    IFERROR(IF(MATCH(0.4,Z542:AW542,0)>0,(D538),1),
    IFERROR(IF(MATCH(0.5,Z542:AW542,0)>0,(D537),1),
    IFERROR(IF(MATCH(0.6,Z542:AW542,0)>0,(D536),1),"")))))))

    =IFERROR(INDEX(Z542:AW542, MATCH(0,Z542:AW542,0)-1)*(1-$B$11),
    IFERROR(IF(MATCH("0.1",Z542:AW542,0)>0,(D541),1),
    IFERROR(IF(MATCH("0.2",Z542:AW542,0)>0,(D540),1),
    IFERROR(IF(MATCH("0.3",Z542:AW542,0)>0,(D539),1),
    IFERROR(IF(MATCH("0.4",Z542:AW542,0)>0,(D538),1),
    IFERROR(IF(MATCH("0.5",Z542:AW542,0)>0,(D537),1),
    IFERROR(IF(MATCH("0.6",Z542:AW542,0)>0,(D536),1),"")))))))

    You do need to change all commas (,) to semi-colons (
    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

    Reply With QuoteRegister To Reply
  5. 06-01-2016, 12:08 PM #5
    Exequiel3k
    Exequiel3k is offline
    Forum Contributor
    Join Date
    05-17-2016
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    115

    Re: Reached the limit of MATCH?

    Thx for the quick replies!

    It seems to be a limit from .xsl, so I switch it to .xsls and used the formulas provided afterwards and problemo solved!

    Had to be diligent on switching to semi-colons too ;p
    Reply With QuoteRegister To Reply
  6. 06-01-2016, 01:18 PM #6
    TMS
    TMS is online now
    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
    44,552

    Re: Reached the limit of MATCH?

    You're welcome.

    Your profile says 2013 and you didn't provide a sample workbook, so we can only guess.

    If you need .xls for compatibility, you'd have a problem.

    Commas (,) and semi-colons ( are regional dependencies.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Nested IF Index Match formula reached limit. Suggestions?
    By patrickt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2015, 03:04 AM
  2. [SOLVED] Have I reached nested if limit on only the 9th nest?
    By pongmeister in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-31-2015, 04:02 PM
  3. [SOLVED] Sum up positive values only from a row until a certain pre-set limit is reached
    By normilet in forum Excel General
    Replies: 19
    Last Post: 03-03-2015, 10:54 AM
  4. [SOLVED] Reached Nested function limit
    By sarah321 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-05-2014, 10:00 AM
  5. Jump to another cell if reached the limit
    By Petijandro in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2013, 08:33 AM
  6. Adding until limit reached
    By deli9680 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-09-2012, 11:20 AM
  7. sheet reached max limit
    By legolas in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 12-14-2011, 06:56 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1