VLookUp -- Maybe?

I have a sheet that lists all our staff and all the columns across list contact information (phone, email, desk info, etc).

I want to create a sheet that assigns staff to a clinical area by dropping down the staff person's name and have all their contact information to share (that is pulled from a separate sheet).


Is there a way I can dropdown the list, click a name, and it pulls all the contact information from another sheet? I have read the vlookup articles but I can get it right in my head.

I plan on "assigning" staff day by day to different clinical areas, but create some reports and a dashboard that gets shared across our organization so staff will know who is covering that area for the day.

The staff listing sheet looks like this:

Any ideas would be super helpful! I may also schedule a pro session for someone to help, I just can't get my brain wrapped around how to make this easy and look nice on the dashboard.

Once I get my grid with the ability to "assign" staff I can get my reports and dashboard created.

Thanks everyone!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Mindy Schneider

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column match in other sheet}, 0))


    So you would build out one formula for each of the columns/data that you want auto-populated based on the Team Coordinator. Then your formula would look something like this:

    =INDEX({Desk Phone Column}, MATCH([Assigned Team Coordinator]@row, {Column with Coordinator}, 0))

    For the next column, just delete and add in a new range at the beginning:

    =INDEX({International Extension Column}, MATCH([Assigned Team Coordinator]@row, {Column with Coordinator}, 0))


    Here are some Help Articles you may find useful:


    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/12/21

    Hi @Mindy Schneider 

    Hope you are fine, yes you can do that by using Index With Match Function. if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.


    My Email for sharing : [email protected]

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Mindy Schneider
    Mindy Schneider ✭✭✭✭

    Hi! Thank you so much for your quick response! I have attempted to share but my organization has this restricted to share outside our domain. Thanks for the offer though!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Mindy Schneider

    if you are an admin or owner you can do that, but i didn't ask to share the original sheet with original data, just save a copy with sample data ( after removing an sensitive data )

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Mindy Schneider

    if you have 5 minute we can talk on Zoom and you can share your screen so i can help you. please send to me your Email so i can send a zoom invitation.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Mindy Schneider

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column match in other sheet}, 0))


    So you would build out one formula for each of the columns/data that you want auto-populated based on the Team Coordinator. Then your formula would look something like this:

    =INDEX({Desk Phone Column}, MATCH([Assigned Team Coordinator]@row, {Column with Coordinator}, 0))

    For the next column, just delete and add in a new range at the beginning:

    =INDEX({International Extension Column}, MATCH([Assigned Team Coordinator]@row, {Column with Coordinator}, 0))


    Here are some Help Articles you may find useful:


    Let me know if this works for you!

    Cheers,

    Genevieve