Index/Match

I have a smartsheet that has a column for "State" and then a column for "Territory". How do I write a function that if a row within column "State" equals say "TX" than the column for "Territory" within the same row = "West". I would need to do this for all states broken into 4 or 5 territories.

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Amy Shank

    Here's one way to do it.

    Have a reference area (or another sheet with the list of states and their territories) and then use this type of formula:

    =INDEX([Territory Ref]:[Territory Ref], MATCH(State@row, [State Ref]:[State Ref], 0))

    I hope this helps you.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Amy Shank
    Amy Shank ✭✭✭✭

    Thank you so much Ramzi! Would that whole formula go in the state column or in the territory column?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Amy Shank

    It would go in the Territory column because the formula is looking for the match from the State column.

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!