How to return a persons title, when name is selected

MGreenPKWY

Can someone help me figure out how to return a person's title/position if their name is selected from a drop down?

Example:

Selected from drop down is "Jane". Jane's title is "VP Ops"

How do I get her title to appear in row 5 if her name is selected in row 4?

KPH

It is possible by setting up a mapping table elsewhere with Jane in one column and VP Ops in another (on the same row) and then create an INDEX and MATCH to match for Jane and return VP Ops.

If you can share a screen shot of your sheet (hiding anything that should not be made public), I can create the formula with your column headings, if you need it.

MGreenPKWY

https://community.smartsheet.com/discussion/comment/404669#Comment_404669

Oh thank you so much!!!!

Here is the snippet

Contact 1 can be Jane and her title would be VP Ops however, contact John's title would be VP Design. Contact 1 would be different based on the project which is why I need Contact 1 Title to pull in something different depending on the selection on Contact 1 Name.

KPH

It looks like you want the title to appear in the column next to the name, not the row below. Is that correct? I hope so, as that is easier!

KPH

Create a sheet with your name and title mappings. Something like this:

On your main sheet insert a formula into the Contact 1 Title field. Because this is a cross sheet formula I am going to talk you through it as you need to set up a cross sheet reference.

Start with =INDEX(

Then click on Reference Another Sheet to set up your cross sheet reference.

In the tree select the sheet you created in step 1. Highlight the Contact Title column and give the reference a name. I called it Mapped Title. Then click Insert reference.

Your formula will now look like this:

This is telling it to pull in the contents of the column in the other sheet that you called Mapped Title.

Now we need to set up the MATCH part to tell it when do do this. Add this part in bold to the formula.

=INDEX({Mapped Title}, MATCH([Contact 1 Name]@row,)

This says we are matching on the Contact 1 Name as it appears in this row (in our case "Jane").

You need to set up a second cross sheet reference for the name column in the mapping sheet to show where the name appears in that sheet. Do this in the same way, but select the other column and give it a different name

Your formula is then

=INDEX({Mapped Title}, MATCH([Contact 1 Name]@row, {Mapped Name}))

You can convert this to a column formula. Any names you put in the name column will have the corresponding title appear.

MGreenPKWY

https://community.smartsheet.com/discussion/comment/404706#Comment_404706

This is amazing! Thank you so much! I am going to work on this today.