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 ✭✭✭✭✭✭
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 ✭✭✭✭✭✭
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
=(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 ✭✭✭✭✭✭
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 ✭✭✭✭✭✭
=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
Categories
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":"