I need help with Index Matching formula

Sylvia R

I have a sheet with contacts listed by organizational role i need to pull contacts into an "owner" column for a project sheet when specified criteria are met:

1- Location/Center Column contains one word from a Location/Center Column in another sheet then i need the email address of the contact.

Paul Newcome

In that case you would use an INDEX/COLLECT.

=INDEX(COLLECT({Column To Pull From}, {Column To Partial Match}, CONTAINS("text", @cell)), 1)

Paul Newcome

INDEX/MATCH generally looks something along the lines of

=INDEX({Column To Pull From}, MATCH([Matching Text]@row, {Column To Match In}, 0))

Sylvia R

Paul thanks - what if the Name in the Matching Text Column is not an exact match for the way the users entered it in the referenced worksheet? I attempted contains versus "match"

Paul Newcome

In that case you would use an INDEX/COLLECT.

=INDEX(COLLECT({Column To Pull From}, {Column To Partial Match}, CONTAINS("text", @cell)), 1)

Sylvia R

Thanks this worked

Paul Newcome

Happy to help. 👍️