+ Reply to Thread
Results 1 to 11 of 11

INDEX/MATCH (multiple critera in multiple rows and columns)

  1. #1
    Registered User
    Join Date
    11-23-2012
    Location
    Chantilly, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    INDEX/MATCH (multiple critera in multiple rows and columns)

    I have a large amount of data that I need to sum in various ways. I have no control over the format of the spreadsheet that contains the data as I receive it from an outside source and need to provide inputs back in this same format. I would like to find a way to write a formula that will return the value in the cell based on multiple criteria. I have two criteria columns and also two header rows that are criteria. I've attached a very simplified version of how the data I receive is formatted. My criteria is in cells A16:A20. I can't seem to hit the right combination of formulas to pull the value from the full range (C3:P11) that matches that criteria. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    With your sample data try...

    =INDEX(C3:P11,MATCH(1,IF(A3:A11=A17,IF(B3:B11=A18,1)),0),MATCH(1,IF(C1:P1=A19,IF(C2:P2=A20,1)),0))

    IMPORTANT
    This is an array formula
    Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
    If entered correctly, the formula will be enclosed in {brackets}
    Do not enter the {brackets} manually
    HTH
    Regards, Jeff

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    Try this formula:

    =SUMPRODUCT(($A$3:$A$11=A17)*($B$3:$B$11=A18)*($C$1:$P$1=A19)*($C$2:$P$2=A20),$C$3:$P$11)

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    11-23-2012
    Location
    Chantilly, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    Both work perfectly. I knew there was something simple I was missing - thanks so much.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,083

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    Glad we could help. Please do not forget to mark your thread as solved.

  6. #6
    Registered User
    Join Date
    02-27-2014
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    I have a similar problem, but the formula did not work for me. I have attached a sample spreadsheet. I need to Match the PIN numbers on the Detail and Payroll and have the totals from the Payroll entered on the correct column of the detail. There will be PIN numbers that do not match or are not listed on the Detail spreadsheet. I also need some kind of formula to highlight the discrepancies.
    Attached Files Attached Files

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    @ccwynar:

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  8. #8
    Registered User
    Join Date
    02-27-2014
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    OK, I am new at this I will open a new thread.

  9. #9
    Registered User
    Join Date
    08-25-2015
    Location
    London, England
    MS-Off Ver
    excel 2013
    Posts
    12

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    Last edited by John90; 07-12-2016 at 10:53 AM.

  10. #10
    Registered User
    Join Date
    07-27-2015
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    1

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    lookup value is 9 and show result


    JOPITER DD231
    ALPHA DD232

    9 JOPITER DD231 D.RED 3.96 25
    9 ALPHA DD232 BLACK 3.96 25
    9 BRAWO DD233 BLUE 3.96 22
    9 MAJEDA DK234 GREEN 3.96 21

    how is it posible

    if one value is repeated which formula we use
    Last edited by ehtishamhussain; 11-01-2016 at 08:36 AM. Reason: upload excel file

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,943

    Re: INDEX/MATCH (multiple critera in multiple rows and columns)

    @ehtishanhussain


    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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