How to return a persons title, when name is selected

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.