+ Reply to Thread
Results 1 to 15 of 15

Index & Multiple match Formula not quite right

  1. #1
    Forum Contributor
    Join Date
    11-20-2009
    Location
    Melbourne
    MS-Off Ver
    Excel 2013
    Posts
    257

    Index & Multiple match Formula not quite right

    In the attached spread sheet I would like to get the answer '1' in Sheet2, cell B15.
    I have put in an INDEX, MATCH, MATCH, MATCH formula that returns a #N/A error.
    Would someone be so kind as to correct my formula so the desired result is returned?
    In the formula I need to know the Division (1) a particular team (Clayton) was in at RAN (10) in 1974.
    I used the INDEX as the Division and the other 3 pieces of information as the MATCHES.
    Thank you for any assistance and advice you can offer.
    Sample1.xlsx

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Index & Multiple match Formula not quite right

    You could try a user defined function.

    Please Login or Register  to view this content.
    Open the VBA editor by hitting Alt F11.
    Insert a new module with Insert - Module
    Paste in the above function
    Go back to the sheet by hitting Alt F11.

    In B15 on sheet2 enter =GetDivision(A15,Sheet1!B2:B26,10,Sheet1!C2:C26,$A$1,Sheet1!E2:E26,Sheet1!D2:D26)

    Remember to save the workbook as a macro enabled workbook .xlsm
    Martin

  3. #3
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Index & Multiple match Formula not quite right

    This should do the trick
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

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

    Re: Index & Multiple match Formula not quite right

    Try it like this with an array* formula:

    =INDEX(Sheet1!D$2:D$26,MATCH(1,(10=Sheet1!C$2:C$26)*($A$1=Sheet1!E$2:E$26)*($A15=Sheet1!B$2:B$26),0))

    *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual <Enter>

    Note that I have used the actual ranges, rather than full-column references, as it is better to keep the ranges as short as possible with array formulae.

    Hope this helps.

    Pete

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index & Multiple match Formula not quite right

    Here is an ARRAY function that will give you what you want...
    =INDEX(Sheet1!$D$2:$D$26,MATCH(Sheet2!A15&Sheet2!$A$1&"U10"&10, Sheet1!$B$2:$B$26&Sheet1!$E$2:$E$26&Sheet1!$A$2:$A$26&Sheet1!$C$2:$C$26,0))...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    10-16-2014
    Location
    Center Line, Michigan, United States
    MS-Off Ver
    MS Office 2013
    Posts
    139

    Re: Index & Multiple match Formula not quite right

    In Cell B15 on Sheet2, place this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Loganeb; 10-26-2014 at 04:03 PM.
    Please click the star (add rep) if I helped!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index & Multiple match Formula not quite right

    or a non-array formula...
    =SUMPRODUCT((Sheet1!$D$2:$D$26)*(Sheet1!$B$2:$B$26=Sheet2!A15)*(Sheet1!$E$2:$E$26=$A$1)*(Sheet1!$A$2:$A$26="U10")*(Sheet1!$C$2:$C$26=10))

    In both my suggestions, you can either hard-code the "U10" and 10, or you can putthem in a cell and reference them for more flexibility

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index & Multiple match Formula not quite right

    Logan, nice work

    2 things to note...
    1, you will probably need to absolute that "search" range at the end (Sheet1!D2:D26)
    2. I think you missed the U10 requirement?

  9. #9
    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,090

    Re: Index & Multiple match Formula not quite right

    Array Formula:

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


    Committed with Ctrl-Shift-Enter rather than just Enter



    Regards, TMS
    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


  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index & Multiple match Formula not quite right

    =INDEX(Sheet1!$D$2:$D$100,MATCH(A15&$A$1&10,INDEX(Sheet1!$B$2:$B$100&Sheet1!$E$2:$E$100&Sheet1!$C$2:$C$100,0),0))
    i dont see where the u10 comes into it as its not in the original requirement!
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index & Multiple match Formula not quite right

    i dont see where the u10 comes into it as its not in the original requirement!
    expandability, I figured this is a test sample of a junior sports team, so there will probably be U10, U12 and more

    If thats not the case, that can obviously be left out

  12. #12
    Forum Contributor
    Join Date
    10-16-2014
    Location
    Center Line, Michigan, United States
    MS-Off Ver
    MS Office 2013
    Posts
    139

    Re: Index & Multiple match Formula not quite right

    Quote Originally Posted by FDibbins View Post
    Logan, nice work

    2 things to note...
    1, you will probably need to absolute that "search" range at the end (Sheet1!D2:D26)
    2. I think you missed the U10 requirement?
    Thank you sir! I had already fixed the absolute range before I saw your comment, lol. I didn't see the U10 requirement, but per your later posts, you're more familiar with sports inner workings and you were thinking ahead.

  13. #13
    Forum Contributor
    Join Date
    11-20-2009
    Location
    Melbourne
    MS-Off Ver
    Excel 2013
    Posts
    257

    Re: Index & Multiple match Formula not quite right

    Thank you so much to everyone that has offered a solution.
    It's amazing how many different solutions there are to something that I thought could only be done one way.
    It is truly a blessing to have so many people willing to offer their knowledge. Thank you once again.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index & Multiple match Formula not quite right

    Thank you for the kind words, they are always appreciated Also, thank you for the feedback, glad we could help

  15. #15
    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,090

    Re: Index & Multiple match Formula not quite right

    You're welcome. Thanks for the rep.

+ 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: 5
    Last Post: 10-16-2016, 02:33 AM
  2. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  3. Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges
    By SimpleJack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2013, 08:54 AM
  4. Index Match formula for multiple lookups
    By RequestGuruHelp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-04-2013, 10:51 PM
  5. multiple match/index formula
    By anguyen27 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-17-2012, 03:49 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