Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Stumped with MATCH & INDEX

  1. #1
    Join Date
    14th July 2012
    Posts
    5

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th November 2006
    Posts
    1,616

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Carim; June 14th, 2017 at 17:25. Reason: added workbook

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th July 2012
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th November 2006
    Posts
    1,616

    Re: Stumped with MATCH & INDEX

    Hello,

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

    Hope this will help
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th November 2006
    Posts
    1,616

    Re: Stumped with MATCH & INDEX

    Hello,

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

    Do not hesitate to share your comments ...

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2012
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th November 2006
    Posts
    1,616

    Re: Stumped with MATCH & INDEX

    Hope attached workbook is in line with your expectations ...
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th July 2012
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th November 2006
    Posts
    1,616

    Re: Stumped with MATCH & INDEX

    Hello again,

    Attached is your file with a tiny correction ...

    Hope this will help
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th July 2012
    Posts
    5

    Re: Stumped with MATCH & INDEX

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Index Match on Array: Unable to get the Match property Error
    By biocentrism in forum Excel General
    Replies: 7
    Last Post: April 30th, 2015, 12:18
  2. Replies: 15
    Last Post: April 25th, 2015, 19:23
  3. Replies: 9
    Last Post: March 18th, 2015, 19:37
  4. Replies: 6
    Last Post: July 13th, 2013, 04:18
  5. Replies: 2
    Last Post: July 21st, 2012, 00:28

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
  •  
porno