Announcement

Collapse
No announcement yet.

Stumped with MATCH & INDEX

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stumped with MATCH & INDEX

    I'm stumped. I have 4 columns of data. I'm needing to match a name from column A in column C, look at the corresponding team number in column B and display the other team members on the same team as the person I'm searching for from column A. Here's a sample of what I'm working with:
    Lookup Team # Team Member Teammates
    Bob 1 Jim Alan, Julia
    Jim 1 Alan Jim, Julia
    Sue 1 Julia Jim, Alan
    Doris 2 Bob Christine, Ralph
    Ralph 2 Christine Bob, Ralph
    Julia 2 Ralph Bob, Christine
    Christine 3 Ryan Doris, Sue
    Ryan 3 Doris Ryan, Sue
    Alan 3 Sue Ryan, Doris
    Any help would be greatly appreciated!

  • #2
    Re: Stumped with MATCH & INDEX

    Hello,

    Do you mean the output you are looking for is Column D with the "other" Team Mates ...

    If it is the case, you will need an array formula to extract all team members and then use substitute() to remove the one already listed ...

    Will prepare a quick sample for you ...

    Edit : see attached example
    Attached Files
    Last edited by Carim; June 14th, 2017, 17:25. Reason: added workbook
    To say "Thank You" for the help received ...Just click on the "Like" icon ...underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Re: Stumped with MATCH & INDEX

      Your sample makes sense. If I don't have column C 'Teammates', and only have columns A&B (Lookup and Team #), is there a way to find the teammates of each individual?

      Lookup Team #
      Bob 1
      Jim 1
      Sue 1
      Doris 2
      Ralph 2
      Julia 2
      Christine 3
      Ryan 3
      Alan 3

      Comment


      • #4
        Re: Stumped with MATCH & INDEX

        Hello,

        Attached is your revised workbook ... with your new requirement ...

        Hope this will help
        Attached Files
        To say "Thank You" for the help received ...Just click on the "Like" icon ...underneath ... ... in the bottom right corner ...

        Comment


        • #5
          Re: Stumped with MATCH & INDEX

          Hello,

          Hope you had a chance to review the latest modification ...

          Do not hesitate to share your comments ...
          To say "Thank You" for the help received ...Just click on the "Like" icon ...underneath ... ... in the bottom right corner ...

          Comment


          • #6
            Re: Stumped with MATCH & INDEX

            My apologies for not replying sooner.


            Woo hoo! That last sample worked well.

            Finally, I have 2 Bobs and 2 Jims in the group (though both don't always participate). I've added last names and I have it somewhat working. Is there an easy way to incorporate the ampersand instead of the comma between names in the 'Team Mates' column (column G) within the Team Mates worksheet so that the team mates would display 'Jim Woodford & Sue Sosna' or 'Bob Fields & Julia Griffin'? I can get the 'Team Mates' column (column G) to display 'Jim Woodford Sue Sosna' (spaces in-between all the names), or I can display 'Jim,Woodford,Sue,Sosna' (commas between all the names) but I haven't figured out how to put an ampersand between the names.

            I've looked at nesting a CONCAT formula within the SUBSTITUTE formula, but it's not going well.

            I attached the workbook with the name updates. If you can help with this last hurdle, it would make my week -- no, my month!

            Thank you so much for all of your help!

            Test TeamMates.xlsm

            Comment


            • #7
              Re: Stumped with MATCH & INDEX

              Hope attached workbook is in line with your expectations ...
              Attached Files
              To say "Thank You" for the help received ...Just click on the "Like" icon ...underneath ... ... in the bottom right corner ...

              Comment


              • #8
                Re: Stumped with MATCH & INDEX

                Any reason when I apply the formulas across the entire list of people (not just the sample you've worked on), the CONCAT formula doesn't output what I'm looking for? I copied over the VB module from the sample and the 'Team Mates' column (Column G in the Team Mates worksheet) outputs properly based on the value in the 'Whole Team' column (Column B in the Team Mates worksheet).

                If Column B would just output properly, I'd have it figured out. Hmmm. So close...


                Test TeamMates V3.xlsm

                Comment


                • #9
                  Re: Stumped with MATCH & INDEX

                  Hello again,

                  Attached is your file with a tiny correction ...

                  Hope this will help
                  Attached Files
                  To say "Thank You" for the help received ...Just click on the "Like" icon ...underneath ... ... in the bottom right corner ...

                  Comment


                  • #10
                    Re: Stumped with MATCH & INDEX

                    Problem(s) solved! Thank you so much! The last fix did the trick.

                    Comment


                    • #11
                      Re: Stumped with MATCH & INDEX

                      Very pleased your formulas are now fully operational ...

                      Thanks a lot ... for your very sincere Thanks ...

                      Very Nice of You ...
                      To say "Thank You" for the help received ...Just click on the "Like" icon ...underneath ... ... in the bottom right corner ...

                      Comment

                      Working...
                      X