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
-
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
-
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.
-
Hi Mark I seem to be getting this error: #INCORRECT ARGUMENT SET
-
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.
-
Mark THANK YOU so much for your help with this. Made my week as I get to tick it off the TODO list!
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!