+ Reply to Thread
Results 1 to 3 of 3

Index/match pulling correctly except for 1st team alphabetically

  1. #1
    Registered User
    Join Date
    05-22-2018
    Location
    Ohio
    MS-Off Ver
    365
    Posts
    2

    Index/match pulling correctly except for 1st team alphabetically

    I have made a workbook for MLB for stats/wins/losses etc, etc.

    On one sheet, I made a current 3 game set between 2 teams, using a formula to get the dates of the 2nd and 3rd games (from another worksheet) based on the manually entered 1st game. The formula I made works fine for all teams except for the first team alphabetically....Arizona (ARI on the spreadsheet). The dates for ARI are the same as the manually entered date for the 1st game.

    I cannot figure out why this is happening.

    The dummy workbook attached has spreadsheets copied directly from my original workbook. All links have been broken.

    Please help, I like to try and figure out my own problems but I'm stumped here.
    Attached Files Attached Files
    Last edited by BuckeyeKaptn; 05-22-2018 at 02:56 PM. Reason: Change to solved

  2. #2
    Registered User
    Join Date
    05-22-2018
    Location
    Ohio
    MS-Off Ver
    365
    Posts
    2

    Re: Index/match pulling correctly except for 1st team alphabetically

    I continued to search as I wasn't getting an answer here, but I found a solution.

    Col T formula was {=IF($H45<>"",(INDEX(Upcoming!$F$2:$F$5513,MATCH(H45&$J45&$L45,Upcoming!$F$2:$F$5513&Upcoming!$K$2:$K$5513&Upcoming!$J$2:$J$5513,-1))),"")}

    Working formula is {=IF($H45<>"",(INDEX(Upcoming!$F$2:$F$5513,MATCH(H45&$J45&$L45,Upcoming!$F$2:$F$5513&Upcoming!$K$2:$K$5513&Upcoming!$J$2:$J$5513,0)-1)),"")}

    I changed where the -1 is (at the end, in bold). The only thing that gives the next game date without regard to opponent.

    Another curious (to me) thing is why was the mistake only on Arizona (ARI) and not on the other teams.

    Can I give myself a star???


  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Index/match pulling correctly except for 1st team alphabetically

    All the dates are wrong accept the ARI as all the others match on the nearest DATE match which gives the dates of 22 May and 23 May (rows 115 and 116).

    By contrast , Your ARI example finds an EXACT match on 21/05/2018 AND teams so this is returned consistently as 21 May 2018.

    As you are comparing first on date, the match effectively ignores the teams (J & K) columns and finds the closest match.

+ 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. [SOLVED] VLOOKUP or INDEX Match for Grades based on Team
    By 3345james in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-18-2016, 03:12 PM
  2. index & match formula is not working correctly
    By integra in forum Excel General
    Replies: 2
    Last Post: 09-15-2015, 08:30 PM
  3. [SOLVED] Index and match formula not pulling data through correctly when using time field
    By Hirad001 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2015, 03:17 AM
  4. Index Match not Formatting Correctly
    By CreamOfWheat in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2015, 07:53 PM
  5. [SOLVED] Index(match not pulling only 2 of five records
    By mmayna03 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-25-2012, 10:45 AM
  6. [SOLVED] Index - match not work correctly
    By excelaron in forum Excel General
    Replies: 2
    Last Post: 07-30-2012, 03:31 PM
  7. Issues With Index/Match Not Working Correctly.
    By mgraesch in forum Excel General
    Replies: 2
    Last Post: 07-31-2009, 10:33 AM

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