+ Reply to Thread
Results 1 to 7 of 7

Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

  1. #1
    Registered User
    Join Date
    12-16-2013
    Location
    MidWest
    MS-Off Ver
    Excel 2010
    Posts
    1

    Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

    I am having problems with multi-criteria Index/Match and SumProduct.
    I can get LOOKUP to work.

    Can you look a test .xlsx file for me?

    This works: =LOOKUP(2,1/($A$2:$A$6=D2)/($B$2:$B$6=E2),($C$2:$C$6))
    This fails: =INDEX($C$2:$C$6,MATCH(D2&E2,$A$2:$A$6&$B$2:$B$6,0)) #VALUE!
    =INDEX($C$2:$C$6,MATCH(D2&E2,A2:A6&B2:B6,0)) #VALUE!
    =INDEX($C$2:$C$6,MATCH(1,(D2=$A$2:$A$6)*(E2=$B$2:$B$6),0)) #N/A
    =INDEX($C$2:$C$6,SUMPRODUCT(($A$2:$A$6=D2)*($B$2:$B$6=E2)*ROW($C$2:$C$6)),0)
    SUMPRODUCT seems to calculate "off a row" and "muffs" either the first or last row.

    Again, I can get it to work. What I expected to get it done failed.

    Bottom line I "burned" a lot of time at MS and other googled sites and LOOKUP is the only one standing.

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

    Looks like you tried to enter array formula. Try to enter the formula with Ctrl + Shift + Enter keys together.

  3. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

    You don't need to enter an array formula if you modify your original to:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

    The formulas you have in columns F H and I are Array formuls that MUST be entered with CTRL + SHIFT + ENTER
    Highlight the cell with the formula, press F2
    Then Press CTRL + SHIFT + ENTER
    When correctly entered, the formula will be enclosed in {brackets}

    The formuals in J K and L will not work because the Row Function returns a row #.
    You're indexing C2:C6, but if it's Row 2 that is matching, then the Row function returns 2 (2 is the 2nd row)
    But, INDEX(C2:C6,2) = C3 (C3 is the 2nd position in C2:C6)

    If the matching value is in C6, then Row = 6
    Index(C2:C6,6) = #REF! because there is no 6th position in C2:C6 (there are only 5 cells in C2:C6)

    Hope that helps clear it up.

  5. #5
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

    Get it done, pls check the file, nothing wrong with formulas, you just missed a few things

    Regards

  6. #6
    Registered User
    Join Date
    04-30-2014
    Location
    CHENNAI
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

    i have a similar problem. I want the reference from other sheet instead of the same sheet.

    I want to match 3 array of sheet 2 and also from other workbook in common server with 3 cells from sheet1 and display the corresponding value.

    Please help out
    Attached Files Attached Files
    Last edited by aruna vijayakumar; 04-30-2014 at 02:24 AM.

  7. #7
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

    It could be wise if U make a new post, do not post in others post....

    Regards
    Azumi

+ 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 with imbedded multi-criteria Match
    By DFELS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2013, 06:46 AM
  2. Replies: 10
    Last Post: 12-18-2012, 07:59 AM
  3. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 PM
  4. Multi Criteria Index and Match - Getting #NA Error
    By abbati77 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2012, 03:22 AM
  5. Replies: 2
    Last Post: 10-03-2011, 01:29 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