How to combine cells from two sheets to get information

I have two sheets where they both have the full name column and I want to get the average numbers a person goes from avature application date to productive date:

Both sheets have full name but I am not sure of the formula to get the average days person (full name) avature application to productive date which is in another sheet.

The formula I tried:

=INDEX(COLLECT({Hired Range 2}, {Hired Range 1}, [Full Name]@row))

Below is the sheet where productive date is the data I want to pull from a cell if the full name is the same as in the other sheet:

Below is the sheet where I want to use the avature application date:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!