+ Reply to Thread
Results 1 to 4 of 4

Index Match 4 criteria perfectly, and not so perfectly

  1. 01-25-2015, 11:38 PM #1
    milkychips
    milkychips is offline
    Registered User
    Join Date
    11-07-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    76

    Index Match 4 criteria perfectly, and not so perfectly

    Hi all
    I am trying to get E2:E13 to return the equivalent cell in L2:L13 if all four of the cells are the same (Team A, Team B, Team A Goals, Team B Goals)

    Two questions.
    1. What is the formula to ensure that it returns a perfect match, an example would be the second row should return K1, because all four are the same
    2. Is there a way I can get it so that it makes a good guess? For example Row 9 has two different Team A names (because one of the teams has since changed i'ts official name). Can I specify that if 3 out of 4 are correct, assume that L9 can be E9

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-25-2015, 11:49 PM #2
    FDibbins
    FDibbins is offline
    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,929

    Re: Index Match 4 criteria perfectly, and not so perfectly

    Try this for 1...
    =INDEX($L$2:$L$13,MATCH(A2&B2&C2&D2,INDEX($M$2:$M$13&$N$2:$N$13&$O$2:$O$13&$P$2:$P$13,0),0))

    For 2, try playing around with this...
    =IFERROR(INDEX($L$2:$L$13,MATCH(A2&B2&C2&D2,INDEX($M$2:$M$13&$N$2:$N$13&$O$2:$O$13&$P$2:$P$13,0),0)),INDEX($L$2:$L$13,MATCH(B2&C2&D2,INDEX($N$2:$N$13&$O$2:$O$13&$P$2:$P$13,0),0)))
    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
    Reply With QuoteRegister To Reply
  3. 01-26-2015, 12:17 AM #3
    milkychips
    milkychips is offline
    Registered User
    Join Date
    11-07-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Index Match 4 criteria perfectly, and not so perfectly

    A thousand thank you's Mr Dibbins
    Reply With QuoteRegister To Reply
  4. 01-26-2015, 01:16 AM #4
    FDibbins
    FDibbins is offline
    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,929

    Re: Index Match 4 criteria perfectly, and not so perfectly

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] My text box autofit was working perfectly
    By tht37 in forum Excel General
    Replies: 18
    Last Post: 04-30-2013, 12:41 AM
  2. VBA now getting Runtime Error 13 - Type Mismatch but used to work perfectly
    By Royzer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2012, 02:29 PM
  3. [SOLVED] Can not work with macros in Excel... But works perfectly in Word
    By Oban in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2012, 06:16 AM
  4. Ranking the data perfectly
    By shinki in forum Excel General
    Replies: 4
    Last Post: 06-10-2009, 05:55 AM
  5. PasteSpecial not working perfectly
    By ctmurray in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-12-2008, 04:20 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1