Index/Match for Comparing Two Sheets

LauraG

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.

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)

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!