Index Match Copy email matching employee IDs

Hello,


I need to fix my formula to copy an employee's email based on their Employee ID numbers matching. I have one sheet where the emails are stored I am abbreviating it as FN in the formula. and EID is employee ID. When I enter this I receive the "INVALID REF" error.


=INDEX({Personal Email FN}, MATCH([Employee ID]@row, {FN-EID}), 0)


I want to do this so I do not have to manually search and enter over 200 emails from one form to another.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Aimee Peyton-Greene

    Try moving the ,0 to inside the MATCH function instead of having it in the INDEX function:

    =INDEX({Personal Email FN}, MATCH([Employee ID]@row, {FN-EID}, 0))

    If this hasn't helped the Invalid Reference error means that there's something going on with your two references:

    {Personal Email FN}

    {FN-EID}

    Check to make sure you've selected the correct range for each of these cross-sheet references.

    If you can confirm that they're looking at the right columns, then see if either of these columns house a cell that has a formula error. (Formula errors can act a bit like dominoes, so if {FN-EID} has one cell with a formula error, it's possible that this is being surfaced in this other formula as well).

    Let me know if any of this helped!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!