VLOOKUP or INDEX,MATCH - how to deal with situation when source will change?

I have a MASTER contact list, which has every contact that we ever have. Primary key is email address.

Then i have a template where a person picks a user from a Contact List and a bunch of columns get populated with phone, city, etc when the email address for the user is matched against the Master.

So that template has been used for about 50 contact list Sheets.

And now I want to add some columns to the Master.

And this means that all of the column indexes are going to change. So I need to edit every one of those contact lists.

Is there a way around this?

*Wouldn't it be cool if there was a mechanism where we could edit a template, and those changes would push out to all of the sheets that were built from that template?

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @James Keuning

    I hope you're well and safe!

    Firstly, have you explored using the Premium App, Control Center? That would have solved the issue above. It's fantastic!

    Secondly, I'd recommend using INDEX/MATCH because it would not be affected by the column order or adding new ones. Still, unfortunately, you'd have to add it to all sheets manually if you don't have Control Center or an API solution or similar.

    Make sense?

    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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @James Keuning

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.


    Or if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.


    My Email for sharing : [email protected]

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @James Keuning

    I hope you're well and safe!

    Firstly, have you explored using the Premium App, Control Center? That would have solved the issue above. It's fantastic!

    Secondly, I'd recommend using INDEX/MATCH because it would not be affected by the column order or adding new ones. Still, unfortunately, you'd have to add it to all sheets manually if you don't have Control Center or an API solution or similar.

    Make sense?

    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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • James Keuning
    James Keuning ✭✭✭✭

    Control Center solves my template pushing problem. And switching to Index/Match solved the columns problem. Thanks!