Need Help with INDEX(COLLECT w/ CONTAINS

Options

Hi All,

I'm trying to populate a column with a global Region value (AMER, APJ, EMEA) based on a local region code contained in an appliance name.

I have two sheets with data.

Sheet 1 contains a column of appliance names and a second column for the Region I want it to populate.
Sheet 2 has two columns, 1 for the Data Center Code (SMF, AMS, etc) and the second with the global Region (AMER, EMEA, etc)

I would like it to populate the global region into sheet 1 based on the Data Center Code contained in the appliance name.

I've tried a few of the other posts n the forum and feel like I'm close but I cant get it to work right.

Tags:

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @Thom555

    The issue here would be you have no identifying information to compare to. You would either need to add the Data center code to sheet 1. Either by pulling it Off of the name or by manually entering it. That said

    =Index({Global Region Ref},Match([Data Center Code]@row,{Data Center Code Ref},0))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @Thom555

    The issue here would be you have no identifying information to compare to. You would either need to add the Data center code to sheet 1. Either by pulling it Off of the name or by manually entering it. That said

    =Index({Global Region Ref},Match([Data Center Code]@row,{Data Center Code Ref},0))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Thom555
    Options

    Thanks @Mark.poole. That helped alot. I also found there was some issues with the data itself causing matching errors. But I got it to work! Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!