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?

Find more posts tagged with

Templates

Formulas

Accepted answers

Andrée Starå

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!

All comments

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 : Bassam.k@mobilproject.it

Andrée Starå

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!

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

James Keuning

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.