Need Help with formula: Join(Collect

05/20/21
Answered - Pending Review

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


Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    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))

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • nresendiznresendiz ✭✭✭✭✭

    Bassam,

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



  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    @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: [email protected]

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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

Sign In or Register to comment.