Need Help with formula: Join(Collect

nresendiz · 05/20/21

I am trying to look up multiple criteria (VENDOR PO), (SAP LINE#) referenced in another sheet and return (CPSD) if it matches the criteria.

current formula below I need to edit the highlighted in yellow so it looks up the vendor name and then matches the (VENDOR PO) and (SAP LINE#) to return the (CPSD)

Below is where my formula resides

Below is where I'm pulling information from

Bassam Khalil · 05/21/21

Hi @nresendiz

Hope you are fine, please use the following formula

=JOIN(COLLECT({pulling information-CPSD}, {pulling information-VENDOR PO}, [VENDOR PO]@row, {pulling information-SAP LINE}, [SAP LINE#]@row))

nresendiz · 05/21/21

Bassam,

Thank you. I have tried that formula but still getting #INCORRECT ARGUMENT SET

Bassam Khalil · 05/25/21

@nresendiz

I think you have a problem with the reference name or columns name you are using with my formula, could you please create a workspace and save a copy of your sheets with sample data and share me as an admin on that workspace to help me to create for you the exact formula.

My Email: Bassam.k@mobilproject.it

Genevieve P. · 05/25/21

Hi @nresendiz

If the JOIN(COLLECT isn't working for you, I would suggest trying an INDEX(COLLECT formula instead. Paul has a great example of this type of formula in this post, here.

=INDEX(COLLECT({CPSD Column}, {VENDOR PO Column}, [VENDOR PO]@row, {SAP LINE Column}, [SAP LINE#]@row), 1)

Keep in mind that {these} each refer to one column in your other sheet. You will need to manually create each cross-sheet reference to find each specific column in the other sheet.

You may also want to ensure that there are no formula errors in the referenced, other sheet. If there's an error in one of those three referenced columns it will create a domino effect and return the error in this formula as well (does that make sense?)

Cheers,

Genevieve

Need Help with formula: Join(Collect

Answers

Help Article Resources

Categories