+ Reply to Thread
Results 1 to 5 of 5

Index and Match with different reference array sizes

  1. #1
    Registered User
    Join Date
    02-01-2017
    Location
    new york, new york
    MS-Off Ver
    2010
    Posts
    3

    Index and Match with different reference array sizes

    I'm using index and match with the following formula: =INDEX($B$95:$F$110,MATCH(L95,$L$95:$L$132,0),1). Unfortunately, it only seems to work sporadically (sometimes skipping rows/gives me a reference error after i drag past the index array). I think the problems are due to the fact that my match lookup array and my index array are different sizes. Any suggestions?

  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
    44,086

    Re: Index and Match with different reference array sizes

    Why would you want to match on a longer array than you index? I would expect that any entry in the matched array, beyond the size of the indexed array, would cause an error. In this case, anything matched beyond row 110.
    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
    02-01-2017
    Location
    new york, new york
    MS-Off Ver
    2010
    Posts
    3

    Re: Index and Match with different reference array sizes

    that is exactly what seems to be happening. it's hard to explain what's going on. would you mind if i sent you the sheet in an email (with a description) so you can take a quick look?

  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
    44,086

    Re: Index and Match with different reference array sizes

    Upload it to the forum.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    02-01-2017
    Location
    new york, new york
    MS-Off Ver
    2010
    Posts
    3

    Re: Index and Match with different reference array sizes

    Sorry for the delayed response. The sheet is attached. The problem I'm running into is located in columns Q and R (only in Account #1 - still building out the rest). For some reason the formula seems to work in column R but i'm still getting an inconsistent formula warning which is concerning. Let me know if you need me to elaborate on what's going on a bit more and thanks in advance for your help.
    Last edited by jpratte1990; 02-03-2017 at 10:56 AM.

+ 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: 3
    Last Post: 04-02-2016, 08:16 PM
  2. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. Replies: 4
    Last Post: 01-13-2014, 04:16 AM
  5. Matching a column of shaft sizes to a column of hole sizes to find best match for all
    By BrettRCourtney in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2013, 11:25 AM
  6. Index Match array equation with sub-array calculation
    By glebbo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2012, 03:04 AM
  7. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 AM

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