+ Reply to Thread
Results 1 to 5 of 5

Look up formula needed for golf competition

  1. #1
    Registered User
    Join Date
    11-06-2013
    Location
    Croydon,England
    MS-Off Ver
    MS 365 Subscription
    Posts
    16

    Look up formula needed for golf competition

    Hi I am new to this and only recently joined the forum. I run Round Robin Competition where everybody plays everybody else once during the year. It gets harder to know who has played whom so far so I am looking for a chart that would show this and spread is attached with two tabs. One "Results" shows date played and outcome Tab two "Who played" shows all 32 players and I need the appropriate box to be filled in with date played ie "10/11/13" for game between Earl & Gale v Freestone & Moore.
    Many thanks if this is possible
    JB Elliot
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Look up formula needed for golf competition

    =IF(ISERROR(MATCH(SUBSTITUTE($A2&B$1," ",""),SUBSTITUTE(results!$E$2:$E$13&results!$I$2:$I$13," ",""),0)),"","X")&IF(ISERROR(MATCH(SUBSTITUTE($A2&B$1," ",""),SUBSTITUTE(results!$I$2:$I$13&results!$E$2:$E$13," ",""),0)),"","X")

    In B2 and copies across down, it's an array formula so needs to be confirmed with Ctrl+shift+enter. It is spelling/case sensitive.

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Look up formula needed for golf competition

    Hi and welcome to the forum!

    First of all, I notice that many of your entrants in the who played tab contain extra spacing and/or differences in speliing to those contained in the results tab (I also noticed that Smith appears in the former without a C or P), which will need to be remedied in order for a meaningful solution to be given.

    That done, and presuming that the "away" pairing is along the top row (couldn't tell!), this formula in B2 and dragged across and down to the rest of the table might be what you require:

    =IF(COLUMNS($A:A)=ROWS($1:1),"",IF(COLUMNS($A:A)<ROWS($1:1),IFERROR(INDEX(results!$A$2:$A$13,MATCH('who played'!$A2&'who played'!B$1,INDEX(results!$E$2:$E$13&results!$I$2:$I$13,,),0)),""),IFERROR(INDEX(results!$A$2:$A$13,MATCH('who played'!B$1&'who played'!$A2,INDEX(results!$I$2:$I$13&results!$E$2:$E$13,,),0)),"")))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    11-06-2013
    Location
    Croydon,England
    MS-Off Ver
    MS 365 Subscription
    Posts
    16

    Re: Look up formula needed for golf competition

    Many thanks XOR LX for your reply and I entered the formula exactly as given and after changing the names and spelling so as they matched all appears OK and many thanks. I also used the other reply sent which worked very well so I have solved my current problem and no doubt will be asking more questions after receiving such great replies that worked. Many thanks again

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Look up formula needed for golf competition

    You're welcome and thanks for the kind feedback.

+ 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] Competition Template...No Spaces in Validation List needed.... manual calculation issues.
    By skyping in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2013, 06:52 PM
  2. [SOLVED] Best 4 Scores from 8 for a Golf Winter League Competition
    By JB1803 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2013, 09:49 AM
  3. Need Help with Formula for Scoring Contestants in a competition
    By NeedScoringHelp in forum Excel General
    Replies: 3
    Last Post: 07-07-2012, 03:31 PM
  4. Formula to show 1st, 2nd place etc...in a competition
    By Alice21 in forum Excel General
    Replies: 17
    Last Post: 02-16-2012, 10:19 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