Index/Match for Comparing Two Sheets

I'm having trouble combining two sheets into one space (either a third sheet or a report) where the data is grouped by a shared index. I have one sheet of contacts (name, email, phone, eligibility), and one sheet that is form-based data (yes/no answers and open-ended text). I want a way to pair our fixed contact details with the respondent data, but I can't create the right formula to unify the indexes.

Can anyone recommend the proper formula to achieve "103" populating where it says #UNPARSEABLE? Once I get that figured out, I think I can get a report to group nicely.

Find more posts tagged with

Accepted answers

MichaelTCA

@LauraG

INDEX/MATCH will allow 1 criteria. If the only criteria you want is to reference the name, then you can use that.

The function will look something like this,

=INDEX({Range of IDs from Source Sheet},MATCH(Name@row from Target Sheet,{Range of Names from Source Sheet},0))

{Provider Contact List Range 4} this is referencing a range from an external source.

[Provider Contact List Range 4]:[Provider Contact List Range 4] this is referencing a range from an internal source. (Note the square brackets)

All comments

MichaelTCA

Hello @LauraG

What is the current function you are trying to use?

LauraG

I know it needs to be some kind of VLOOKUP or INDEX/MATCH, but I can't figure out the right syntax to grab the data from my contact list. Is it {Provider Contact List Range 4} or {Provider Contact List Range 4}:{Provider Contact List Range 4}? The samples I found in the help articles don't look exactly the same for cross-sheet lookups.

MichaelTCA

@LauraG

INDEX/MATCH will allow 1 criteria. If the only criteria you want is to reference the name, then you can use that.

The function will look something like this,

=INDEX({Range of IDs from Source Sheet},MATCH(Name@row from Target Sheet,{Range of Names from Source Sheet},0))

{Provider Contact List Range 4} this is referencing a range from an external source.

[Provider Contact List Range 4]:[Provider Contact List Range 4] this is referencing a range from an internal source. (Note the square brackets)

LauraG

Thank you!! It finally clicked!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.