VLookUp -- Maybe?

Mindy Schneider · 08/12/21

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!

Genevieve P. · 08/12/21

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

Bassam Khalil · 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 : Bassam.k@mobilproject.it

Mindy Schneider · 08/12/21

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 · 08/12/21

@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 )

Bassam Khalil · 08/12/21

@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.

Genevieve P. · 08/12/21

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

VLookUp -- Maybe?

Best Answer

Answers

Categories