INDEX MATCH with multiple values question

Hello,


I have 2 sheets.

Response Form

Assignment tracker


Both sheets have the same following columns:

Evaluator Name

Candidate Name


I have a check box column in Assignment Tracker that I'd like to check off if the Candidate name AND the Evaluator name match in both sheets.


There will be duplicate results for each column but the pair should always be unique. The formula I have doesn't work as it only checks the very first instance of either and stops there. Is there a way to write a formula that will stop once BOTH values are present?


Here's my formula:

=IFERROR(IF([Evaluator Name]@row = INDEX({FY24 Hirevue Response Sheet Range 1 }, MATCH([HV Candidate Name]@row, {FY24 Hirevue Response Sheet Range 2}, 0)), 1), "")

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I think I would just use a COUNTIFS in this situation:

    =IF(COUNTIFS({FY24 Hirevue Response Sheet Range 1}, [Evaluator Name]@row, {FY24 Hirevue Response Sheet Range 2}, [HV Candidate Name]@row) > 0, 1, "")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hello @Anthony S.<\/a>,<\/p>

I believe you need to put brackets around your column references, so the updated formula would be:<\/p>

=SUMIFS({Quantity}, {Product}, [Product]$1, {Month}, [Month]$1, {Vendor}, [Vendor]$1)<\/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":109252,"type":"question","name":"RAID Log Date Value Locking per column when status changes","excerpt":"Hello everyone, I have tried to find the best answer for this but I see too many threads and different responses. If anyone could help as I am new to this. I am only looking to lock in a date value on 2 columns whenever there is a status change, from when an issue is raised and then resolved. I know there is a record a…","snippet":"Hello everyone, I have tried to find the best answer for this but I see too many threads and different responses. If anyone could help as I am new to this. I am only looking to…","categoryID":322,"dateInserted":"2023-08-22T07:34:24+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T11:49:33+00:00","insertUserID":165418,"insertUser":{"userID":165418,"name":"Jsantos","title":"Technical services","url":"https:\/\/community.smartsheet.com\/profile\/Jsantos","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T10:15:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T15:25:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385396437,"url":"https:\/\/community.smartsheet.com\/discussion\/109252\/raid-log-date-value-locking-per-column-when-status-changes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109252\/raid-log-date-value-locking-per-column-when-status-changes","format":"Rich","tagIDs":[319,437,448],"lastPost":{"discussionID":109252,"commentID":391803,"name":"Re: RAID Log Date Value Locking per column when status changes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391803#Comment_391803","dateInserted":"2023-08-22T11:49:33+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T15:25:08+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-22T12:39:56+00:00","dateAnswered":"2023-08-22T08:42:10+00:00","acceptedAnswers":[{"commentID":391778,"body":"

Hi @Jsantos<\/a> <\/p>

For the record a date automation option, it can work for both if you have separate date columns for when the row was raised and resolved. For each you'd set it for when the row was changed to the relevant status and have the automation record the date in the appropriate column.<\/p>

You can also set a row lock automation if you wish (though be aware that the sheet owner & admins can still edit the line).<\/p>

Example:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Obviously your sheet will have more columns, but the accompanying automations for this would be:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

If rows are being created when raised only, you can also used the system generated \"Created date\" column, and similarly you can use the \"Modified date\" & slightly modified lock automation if you wanted date time (changed the condition to the status being resolved).<\/p>

Hope this helps, but if you've any questions etc. then just post! 😊<\/span><\/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":319,"urlcode":"functionality","name":"functionality"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}]},{"discussionID":109237,"type":"question","name":"Using multiple IF(AND) statements in a formula","excerpt":"Hello, I am trying to create a column formula that will update Schedule health to a red, yellow, green, or blue circle using multiple IF and IF(AND) statements, but I continuously get the error message \"#INCORRECT ARGUMENT SET\" Here is my formula: =IF(IF(AND([End Date]@row - [Today's Date]@row <= 0, Status@row = \"In…","snippet":"Hello, I am trying to create a column formula that will update Schedule health to a red, yellow, green, or blue circle using multiple IF and IF(AND) statements, but I continuously…","categoryID":322,"dateInserted":"2023-08-21T21:09:42+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T14:02:47+00:00","insertUserID":163744,"insertUser":{"userID":163744,"name":"AdamsR","title":"Regulatory and Development Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/AdamsR","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T14:37:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163744,"lastUser":{"userID":163744,"name":"AdamsR","title":"Regulatory and Development Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/AdamsR","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T14:37:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3385366349,"url":"https:\/\/community.smartsheet.com\/discussion\/109237\/using-multiple-if-and-statements-in-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109237\/using-multiple-if-and-statements-in-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109237,"commentID":391832,"name":"Re: Using multiple IF(AND) statements in a formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391832#Comment_391832","dateInserted":"2023-08-22T14:02:47+00:00","insertUserID":163744,"insertUser":{"userID":163744,"name":"AdamsR","title":"Regulatory and Development Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/AdamsR","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T14:37:48+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\/HIPWQ63RW1TU\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T14:01:52+00:00","dateAnswered":"2023-08-21T22:21:01+00:00","acceptedAnswers":[{"commentID":391743,"body":"

Give this a try:<\/p>

=IF(Status@row = \"Complete\", \"Blue\", IF(Status@row = \"In Progress\", IF([End Date]@row< TODAY(), \"Red\", IF([End Date]@row< TODAY(30), \"Yellow\", \"Green\")))<\/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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions