+ Reply to Thread
Results 1 to 7 of 7

Why does Index and Match Formula work for Named Range.. Except The Last Rows?

  1. 07-27-2018, 12:46 PM #1
    jrtaylor
    jrtaylor is offline
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Why does Index and Match Formula work for Named Range.. Except The Last Rows?

    I've attached a workbook with two tabs. I've deleted unnecessary formulas/cells in order to present the problem more clearly.

    In the "Sum" tab, I have a table which organizes and consolidates data in a larger, much longer table in the second tab, titled "TradeStrat".

    Columns I and J of the Sum tab use index and match formulas to accomplish this. These formulas now work perfectly for every row... down to the last one (row 212). This is where they break and I can't figure out why.

    I've inserted a comment which shows you what the values should be for those two cells.

    Any advice that solves this problem and does not break the results in the rows above? (I'm open to an entirely new formula as long as it works for all the rows above).

    I'm happy to clarify anything if you have questions.

    Thank You
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-27-2018, 12:55 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,717

    Re: Why does Index and Match Formula work for Named Range.. Except The Last Rows?

    Because it's matching the first instance of that number in BE43 on the lookup sheet. You need to start the arrays after that.

    =IF(A212="","",INDEX(TradeStrat!$BF44:$BF5000,MATCH(INDEX(TradeStrat!$BJ44:$BJ5000,MATCH(A212,TradeStrat!$BE44:$BE5000,0)),TradeStrat!$BI44:$BI5000,0)))
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-27-2018, 12:59 PM #3
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,498

    Re: Why does Index and Match Formula work for Named Range.. Except The Last Rows?

    I was wondering what the additional parts are returning that this =IF(A212="","",INDEX(TradeStrat!$BF45:$BF1900,MATCH(A212,TradeStrat!$BE45:$BE1900,0))) can't return instead?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 07-27-2018, 01:05 PM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,717

    Re: Why does Index and Match Formula work for Named Range.. Except The Last Rows?

    So in I30 copied down:

    =INDEX(TradeStrat!$BF45:$BF5000,MATCH(INDEX(TradeStrat!$BJ45:$BJ4000,MATCH(A30,TradeStrat!$BE45:$BE5000,0)),TradeStrat!$BI45:$BI5000,0))

    I don't see any named ranges here, however.
    Reply With QuoteRegister To Reply
  5. 07-27-2018, 01:26 PM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,717

    Re: Why does Index and Match Formula work for Named Range.. Except The Last Rows?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  6. 07-27-2018, 02:13 PM #6
    jrtaylor
    jrtaylor is offline
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: Why does Index and Match Formula work for Named Range.. Except The Last Rows?

    Yes, thank you that solved it. Thank you!
    Reply With QuoteRegister To Reply
  7. 07-27-2018, 02:26 PM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,717

    Re: Why does Index and Match Formula work for Named Range.. Except The Last Rows?

    Glad to have helped!
    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. How to get MATCH() to work with non contguous named range
    By tsrwebman in forum Excel General
    Replies: 14
    Last Post: 02-08-2017, 02:55 PM
  2. Named range in Index & Match
    By edward_glyver in forum Excel General
    Replies: 10
    Last Post: 04-29-2016, 08:11 AM
  3. [SOLVED] INDEX MATCH VLOOKUP Named Range
    By overbomb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-03-2015, 12:56 PM
  4. Dynamic Named Range + Index/Match Returning Circular Reference
    By jennarenae in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2014, 06:23 PM
  5. [SOLVED] Reference to named range to be used in index match formula!! Help Please!!
    By Optimum in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-24-2013, 06:54 AM
  6. Cancel posting
    By Richard3 in forum Excel General
    Replies: 1
    Last Post: 02-24-2012, 12:45 PM
  7. Named range lookup using Index and match
    By Sirrob01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2007, 11:15 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1