+ Reply to Thread
Results 1 to 8 of 8

INDEX MATCH for multiple rows not working

  1. #1
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    INDEX MATCH for multiple rows not working

    Hi

    I have an index match array in the attached workbook. It works for the first row, but when I copy it down, it is returning an error.

    Please see attached sample workbook for what I am trying to do. If anyone can assist me with this, that would be really appreciated.

    I also realise I could use SUMIFS but not sure if that is easier.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: INDEX MATCH for multiple rows not working

    Hi there. Try htis (ordinary, i.e. non-array) formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: INDEX MATCH for multiple rows not working

    In your formula the 'Lookup List' was on the wrong cell references, it was this

    =INDEX('Lookup List'!D2:D14,MATCH(1,(A$2:A$13='Lookup List'!A2)*(B$2:B$13='Lookup List'!B2)*(C$2:C$13='Lookup List'!C2),0))

    and should be this

    =INDEX('Lookup List'!D$2:D$14,MATCH(1,('Lookup List'!A$2:A$14=A2)*('Lookup List'!B$2:B$14=B2)*('Lookup List'!C$2:C$14=C2),0))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: INDEX MATCH for multiple rows not working

    thank you so much

    for some reason though, I cannot get it to work in my master workbook (see attached). Formulas should appear in from AL26 onwards. The first one works but not the rest.

    Have I missed something here?
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: INDEX MATCH for multiple rows not working

    I tried this, after adjusting for the different sheet name and cell ranges. It seems OK to me

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: INDEX MATCH for multiple rows not working

    In your formula, the ranges for the references in meeting data ended at different rows (must be the same) and the lookup cells in output were referring to row 3 not the (new) starting row, row 26.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: INDEX MATCH for multiple rows not working

    thank you

    seems I wasn't including both sheets as a reference (only meeting data).

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: INDEX MATCH for multiple rows not working

    You're welcome.

+ 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. Index and Match across multiple rows and columns look up not working properly
    By jollyfella in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-09-2015, 12:06 AM
  2. [SOLVED] Multiple IF's & Index/Match - Not working properly
    By Pooger in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-10-2015, 04:01 PM
  3. [SOLVED] Vlookup/Index, Match - Not working after so many rows.
    By Alias1431 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-05-2013, 02:51 PM
  4. [SOLVED] Index Match Multiple Criteria not working
    By garciapliz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-22-2013, 12:07 PM
  5. [SOLVED] INDEX/MATCH not working for multiple criteria
    By lukesanborn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-27-2012, 09:20 PM
  6. Index - Match - ROWS formula not working?
    By marvinlajiant in forum Excel General
    Replies: 5
    Last Post: 10-12-2011, 11:26 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1