How to return a persons title, when name is selected

Options

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?

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/13/23
    Options

    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
    MGreenPKWY ✭✭✭✭
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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
    KPH ✭✭✭✭✭✭
    Options


    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
    MGreenPKWY ✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!