Index Match Match Failure

Hi, I am having trouble with an INDEX, MATCH, MATCH formula.

Basically what I want to do is say.

If the PROCUREMENT COUNTRY and the DIV @ROW match then return the PROCUREMENT MANAGER from the DIVISION ASSIGNMENT BREAKDOWN sheet.

=INDEX({DIVISION ASSIGNMENT BREAKDOWN Range 2}, MATCH([PROCUREMENT COUNTRY]@row, {DIVISION ASSIGNMENT BREAKDOWN Range 1}, 0), MATCH(DIV@row, {DIVISION ASSIGNMENT BREAKDOWN Range 3}, 0))

It works up until I add the second match then it returns an #INVALID VALUE

Any help would be greatly appreciated.

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Ooops. I see what I did. Try JOIN instead. If there's only 1 match in the range it should work for you:

    =JOIN(COLLECT({DIVISION ASSIGNMENT BREAKDOWN Range 2}, {DIVISION ASSIGNMENT BREAKDOWN Range 1},@cell=[PROCUREMENT COUNTRY]@row, {DIVISION ASSIGNMENT BREAKDOWN Range 3}, @cell=DIV@row)

    Any luck this time?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Geoff,

    Try INDEX(COLLECT instead.

    =INDEX(COLLECT({DIVISION ASSIGNMENT BREAKDOWN Range 2}, {DIVISION ASSIGNMENT BREAKDOWN Range 1},[PROCUREMENT COUNTRY]@row, {DIVISION ASSIGNMENT BREAKDOWN Range 3}, DIV@row)

    Work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Geoff Baker
    Geoff Baker ✭✭✭✭

    Hi Mark I seem to be getting this error: #INCORRECT ARGUMENT SET

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Ooops. I see what I did. Try JOIN instead. If there's only 1 match in the range it should work for you:

    =JOIN(COLLECT({DIVISION ASSIGNMENT BREAKDOWN Range 2}, {DIVISION ASSIGNMENT BREAKDOWN Range 1},@cell=[PROCUREMENT COUNTRY]@row, {DIVISION ASSIGNMENT BREAKDOWN Range 3}, @cell=DIV@row)

    Any luck this time?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Geoff Baker
    Geoff Baker ✭✭✭✭

    Mark THANK YOU so much for your help with this. Made my week as I get to tick it off the TODO list!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    So it worked or you found your own solution. Either way, perfect! Appreciate you contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Geoff Baker
    Geoff Baker ✭✭✭✭

    Mark your revised solution worked perfectly :)

  • @Mark Cronk - looks like you were able to provide some excellent help here. I have a similar situation I am hoping you can help me with?

    I am trying to do a cross-sheet formula to look at two columns 1) ProductType and 2) Productsub-group. If the details match in one, I want to populate details from a 3rd column 'Min.Enrollment'.

    I've tried IF, VLOOKUP, and INDEX/MATCH and can't get them to work. Any suggestions?

    Thanks,

    Alex

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Alex Knisely

    I hope you're well and safe!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!