IFERROR, INDEX.AND, and MATCH function in one formula

嗨all

plz check my formula

=IFERROR(INDEX({Daftar Sales Order in Progress Range 1}; AND(MATCH([email protected]; {Daftar Sales Order in Progress Range 5}; 1); MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 1))); "No Data")


basically i want to return a value of saya column A, if column B and column C in source sheet and current sheet is matching.


but somehow all is #NO MATCH, in this case i use IFERROR so it turn out "No Data"


UPDATE

i change the formula to

=INDEX({Daftar Sales Order in Progress Range 0}; AND(MATCH([email protected]; {Daftar Sales Order in Progress Range 5}); MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4})))

the result is #INVALID REF

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hi Andri

    Index Match only works with one set of criteria. When you have multiple criteria that need to met as part of the 'match', we use INDEX COLLECT. By default, every term listed within a COLLECT function is an 'AND' condition.

    = IFERROR(指数(收集({Daftar学监的销售订单ress Range 1};{Daftar Sales Order in Progress Range 5},@[email protected], {Daftar Sales Order in Progress Range 4},@cell=[Nomor Kapling]@row);1);"No Data")

    Kelly

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi@andri witana,

    Try:

    =IFERROR(INDEX({Daftar Sales Order in Progress Range 1}; IF(MATCH([email protected]; {Daftar Sales Order in Progress Range 5}; 0)=MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), "")), "No Data")

    I didn't test it so you might need to do some tweaking.

    Any help?

    Mark


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

  • @KDM

    this formula

    = IFERROR(指数(收集({Daftar学监的销售订单ress Range 1};{Daftar Sales Order in Progress Range 5},@[email protected], {Daftar Sales Order in Progress Range 4},@cell=[Nomor Kapling]@row);1);"No Data")

    the result is = #unparseable

    to better see where the problem is, i dont use IFERROR now

    =指数(有限公司LLECT({Daftar Sales Order in Progress Range 1}; {Daftar Sales Order in Progress Range 5};[email protected]; {Daftar Sales Order in Progress Range 4}; =[Nomor Kapling]@row); {Daftar Sales Order in Progress Range 1})

    this formula result is #invalid data type

    @Mark Cronk

    =(INDEX({Daftar Sales Order in Progress Range 1}; IF(MATCH([email protected]; {Daftar Sales Order in Progress Range 5}; 0)=MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), ""))

    the result is #unparseable

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    @andri witana

    I think some terms were removed that were part of the INDEX/COLLECT when you removed the IFERROR.

    Try this please

    =指数(有限公司LLECT({Daftar Sales Order in Progress Range 1};{Daftar Sales Order in Progress Range 5};@[email protected]; {Daftar Sales Order in Progress Range 4};@cell=[Nomor Kapling]@row);1)

    Also, when you created these ranges - is each range it's own single column? Please note you can rename the ranges so instead of them saying 'Range 4' for example, you can reference the column they refer to.

  • =指数(有限公司LLECT({Daftar Sales Order in Progress Range 1};{Daftar Sales Order in Progress Range 5};@[email protected]; {Daftar Sales Order in Progress Range 4};@cell=[Nomor Kapling]@row);1)

    this formula result is #invalid value

    the ranges is single column, i clicked the blue colored reference another sheet.

    thx before@KDM

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    =INDEX({Daftar Sales Order in Progress Range 1}; IF(MATCH([email protected]; {Daftar Sales Order in Progress Range 5}; 0)=MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), ""))


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

  • @KDMand@Mark Cronkthx for your assistance

    i understand how to use index, collect and match better

    for my problem i decide to create a new column that combine key identifier column

    then i applied this formula

    =IFERROR(INDEX({Link SO}; (MATCH([Kode Kapling]@row; {Daftar SO}))); "")

    works, and less formula to check on.

    thx a lot u both

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
=[End Date]@row - [Start Date]@row<\/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":108271,"type":"question","name":"Conditional formula - if a date is less than 30 days from today","excerpt":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula. =IF(AND([Event Date] > TODAY(), [Event Date] <= TODAY() + 30), \"Less than 30 days from today\", \"More than 30 days from today\") Help?","snippet":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula.…","categoryID":322,"dateInserted":"2023-07-28T18:21:58+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":159884,"lastUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":23,"score":null,"hot":3381143059,"url":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","format":"Rich","lastPost":{"discussionID":108271,"commentID":387894,"name":"Re: Conditional formula - if a date is less than 30 days from today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387894#Comment_387894","dateInserted":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+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-07-28T19:21:56+00:00","dateAnswered":"2023-07-28T18:55:42+00:00","acceptedAnswers":[{"commentID":387890,"body":"

Try this:<\/p>

=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/75145/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"

Greetings @brownrobe<\/a>,<\/p>

Here is a possible solution for your formula:<\/p>

=IF(NOT(ISBLANK([Recovery Collected Date]@row)), \"Complete\", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), \"Incomplete\", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), \"Pending\", \"//www.santa-greenland.com/community/discussion/75145/\")))<\/p>

Please confirm I have your column names correct and adjust as needed.<\/p>

I hope this helps, and have a great weekend.<\/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":[]}],"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