I need help with a formula


I am trying to derive a formula that if the value in column a is entered, then column be will populate to the assigned email address. Can you assist?



  • KPH
    KPH ✭✭✭✭✭✭

    Hi @cburnett

    If I have understood correctly, you have a sheet with column A and B as pasted above. Then in another sheet, you want to be able to enter a value from column A and have another column populate with the value from column B.

    So, your output in the second sheet would look like this:

    If so, you can do this with an INDEX and MATCH function.

    If these new columns were in the same sheet as A and B, the formula would be:

    =INDEX(B:B, MATCH([Enter A here]@row, A:A))

    Where Enter A here is the column name

    If the new columns are in a different sheet you will need to replace B:B and A:A with cross sheet references.

    =INDEX(B:B, MATCH([Enter A here]@row, A:A))

    If you paste that formula in and then click on the references to change, a pop up will guide you through creating a cross sheet reference:

    Your formula will end up looking something like this (depending on the names you give your cross sheet references).

    =INDEX({Assignment Sheet B}, MATCH([Enter A here]@row, {Assignment Sheet A}))

    Hope that helps.

  • This is exactly what I was looking for. Thank you very much!

  • KPH
    KPH ✭✭✭✭✭✭

    Great! Thanks for letting me know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!