Nesting INDEX/MATCH
Here's a pretty version of the formula I'm trying to implement:
I can nest two INDEX/MATCHs and it works fine:
=IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0))), "")
But if I try to do three or more, I get #INCORRECT ARGUMENT SET:
=IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0)), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0))), "")
or ultimately:
=IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0)), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0)), INDEX({FL Region Review Range 1}, MATCH([RE Store # Lookup]@row, {FL Region Review Range 2}, 0)), INDEX({Great Lakes Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review Range 2}, 0)), INDEX({HI Region Review Range 1}, MATCH([RE Store # Lookup]@row, {HI Region Review Range 2}, 0)), INDEX({NE and NYC Review Range 1}, MATCH([RE Store # Lookup]@row, {NE and NYC Review Range 2}, 0)), INDEX({NW Region Review Range 1}, MATCH([RE Store # Lookup]@row, {NW Region Review Range 2}, 0)), INDEX({South Region Review Range 1}, MATCH([RE Store # Lookup]@row, {South Region Review Range 2}, 0)), INDEX({Southern OH Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Southern OH Region Review Range 2}, 0)), INDEX({TX Region Review Range 1}, MATCH([RE Store # Lookup]@row, {TX Region Review Range 2}, 0))), "")
Is there a limit or is this a syntax issue?
Thank you, Robert
Best Answer
-
Robert Francher ✭✭✭✭I co-worker helped me figure this out. Here's the final formula:
=IFERROR(INDEX({CA Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {CA Region Review - Store #}, 0)),
IFERROR(INDEX({Carolinas Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review - Store #}, 0)),
IFERROR(INDEX({Central Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Central Region Review - Store #}, 0)),
IFERROR(INDEX({FL Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {FL Region Review - Store #}, 0)),
IFERROR(INDEX({Great Lakes Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review - Store #}, 0)),
IFERROR(INDEX({HI Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {HI Region Review - Store #}, 0)),
IFERROR(INDEX({NE and NYC Review - Store Exit Date}, MATCH([RE Store # Lookup]@row, {NE and NYC Review - Store #}, 0)),
IFERROR(INDEX({NW Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {NW Region Review - Store #}, 0)),
IFERROR(INDEX({South Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {South Region Review - Store #}, 0)),
IFERROR(INDEX({Southern OH Region - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Southern OH Region - Store #}, 0)),
IFERROR(INDEX({TX Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {TX Region Review - Store #}, 0)), "")))))))))))
Answers
-
David Joyeuse ✭✭✭✭✭The IFERROR function works like this:
(first do this, if you have an error doing this then do that instead).
I've counted 11 INDEX/MATCH function. So you're missing a bunch of IFERROR statements in your formula. 8 I think.
So you have to rework your formula adding all those IFERROR function to what you want it to do.
Hope it helped!
-
Robert Francher ✭✭✭✭
I tried the following but got the same result:
= IFERROR (IFERROR (IFERROR (IFERROR (IFERROR (IFERROR(我FERROR(IFERROR(IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0)), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0)), INDEX({FL Region Review Range 1}, MATCH([RE Store # Lookup]@row, {FL Region Review Range 2}, 0)), INDEX({Great Lakes Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review Range 2}, 0)), INDEX({HI Region Review Range 1}, MATCH([RE Store # Lookup]@row, {HI Region Review Range 2}, 0)), INDEX({NE and NYC Review Range 1}, MATCH([RE Store # Lookup]@row, {NE and NYC Review Range 2}, 0)), INDEX({NW Region Review Range 1}, MATCH([RE Store # Lookup]@row, {NW Region Review Range 2}, 0)), INDEX({South Region Review Range 1}, MATCH([RE Store # Lookup]@row, {South Region Review Range 2}, 0)), INDEX({Southern OH Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Southern OH Region Review Range 2}, 0)), INDEX({TX Region Review Range 1}, MATCH([RE Store # Lookup]@row, {TX Region Review Range 2}, 0))))))))))), "")
I appreciate the feedback. I'll keep working on it.
-
Robert Francher ✭✭✭✭
I'm getting #INCORRECT ARGUMENT SET using to search 3 or more:
=IFERROR(IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0)), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0)))), "")
-
David Joyeuse ✭✭✭✭✭
#INCORRECT ARGUMENT SET means your formula isn't correctly written.
=IFERROR(IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0))),INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0)))
Since you're nesting IFERRORS on the first argument, your parenthesis cannot be stacked at the end of the formula. You have to close them after each INDEX/MATCH argument.
= IFERROR (IFERROR (IFERROR (IFERROR (IFERROR (IFERROR(我FERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({CA Region Review Range 1}, MATCH([RE Store # Lookup]@row, {CA Region Review Range 2}, 0)), ""),INDEX({Carolinas Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review Range 2}, 0))), INDEX({Central Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Central Region Review Range 2}, 0))), INDEX({FL Region Review Range 1}, MATCH([RE Store # Lookup]@row, {FL Region Review Range 2}, 0))), INDEX({Great Lakes Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review Range 2}, 0))), INDEX({HI Region Review Range 1}, MATCH([RE Store # Lookup]@row, {HI Region Review Range 2}, 0))), INDEX({NE and NYC Review Range 1}, MATCH([RE Store # Lookup]@row, {NE and NYC Review Range 2}, 0))), INDEX({NW Region Review Range 1}, MATCH([RE Store # Lookup]@row, {NW Region Review Range 2}, 0))), INDEX({South Region Review Range 1}, MATCH([RE Store # Lookup]@row, {South Region Review Range 2}, 0))), INDEX({Southern OH Region Review Range 1}, MATCH([RE Store # Lookup]@row, {Southern OH Region Review Range 2}, 0))), INDEX({TX Region Review Range 1}, MATCH([RE Store # Lookup]@row, {TX Region Review Range 2}, 0)))
Should be this if you want to display "" when no INDEX/MATCH works.
Hope it helped!
-
Robert Francher ✭✭✭✭
This forum is the best place ever. I'm testing this out on a small number of records. In the screenshot below, Store # 3201 does not have a match but 4339 and 4341 do. With the new formula, all three are blank:
If I plug in the single INDEX/MATCH for the sheet where I know there's a match, it brings back a date:
Any ideas? Thanks again for your time.
R
-
Robert Francher ✭✭✭✭
I co-worker helped me figure this out. Here's the final formula:
=IFERROR(INDEX({CA Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {CA Region Review - Store #}, 0)),
IFERROR(INDEX({Carolinas Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Carolinas Region Review - Store #}, 0)),
IFERROR(INDEX({Central Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Central Region Review - Store #}, 0)),
IFERROR(INDEX({FL Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {FL Region Review - Store #}, 0)),
IFERROR(INDEX({Great Lakes Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Great Lakes Region Review - Store #}, 0)),
IFERROR(INDEX({HI Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {HI Region Review - Store #}, 0)),
IFERROR(INDEX({NE and NYC Review - Store Exit Date}, MATCH([RE Store # Lookup]@row, {NE and NYC Review - Store #}, 0)),
IFERROR(INDEX({NW Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {NW Region Review - Store #}, 0)),
IFERROR(INDEX({South Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {South Region Review - Store #}, 0)),
IFERROR(INDEX({Southern OH Region - Site Exit Date}, MATCH([RE Store # Lookup]@row, {Southern OH Region - Store #}, 0)),
IFERROR(INDEX({TX Region Review - Site Exit Date}, MATCH([RE Store # Lookup]@row, {TX Region Review - Store #}, 0)), "")))))))))))
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":18,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
