Formula Help! INDEX/COLLECT

Hi,
I am attempting to cross-reference contacts with multiple contacts from a helper sheet to the Vendor II column pictured below. I have three different "Businesses" and eight different "Markets" and specific contacts align to each "Business/Market".
Example:
LTSS and New York: Specific Contacts
LTSS and Nevada: Specific Contacts
HBMC and New York: Specific Contacts
Attempted Formulas:
#UNPARSEABLE
=INDEX(COLLECT({Vendor Contact Production Range 1}, Business@row, {Vendor Contact Production Range 2}, Market@row, {Vendor Contact Production Range 3}, 1)))
=IFERROR(INDEX(COLLECT({Vendor Contact Production Range 1}, Business@row, {Vendor Contact Production Range 2}, Market@row, {Vendor Contact Production Range 3}, 0))"")
=JOIN(COLLECT({Vendor Contact Production Range 1}, Business@row, {Vendor Contact Production Range 2}, Market@row, {Vendor Contact Production Range 3}, 1)")")
#INCORRECT ARGUMENT SET
=IFERROR(INDEX(COLLECT({Vendor Contact Production Range 1}, Business@row, {Vendor Contact Production Range 2}, Market@row, {Vendor Contact Production Range 3}, 1)))
The Vendor Contact column shown below is incorrect as it is solely based on the "Market" without considering the specific business. Any guidance is appreciated.
Answers
-
People can feel free to correct me if I'm wrong but I don't think you can use an index collect formula on a contact column with multiple contacts
See this previous post on the topic
https://community.smartsheet.com/discussion/111668/add-multiple-contacts-to-one-column
-
@tcleaning, much appreciated!
Help Article Resources
Categories
Check out the Formula Handbook template!