Help getting value to appear
Here is my equation. I am trying to get it to match the columns between two spreadsheets and display a value based on cells matching. It's not giving me an error message and it looks like all the references are correct, but it's not displaying a result.
=IFERROR(INDEX({PVSYST Forecast}, MATCH(1, ([Project Name]@row = {Project Name}) * ([email protected]= {Month}), 0)), "")
This is the sheet the equation is on. Asking it to look at these cells...
Then I am asking it to compare to these columns, find a row that has a matching data within Month and Project Name and then to pull the result where we have a match in the row. The result should pull the PVSYST forcast column
Here is where it's supposed to be displaying.
What am I doing wrong? I am sitting here with CHAT GBT for hours trying to figure it out and have tried it all different ways to work.
Best Answer
-
Kelly Moore ✭✭✭✭✭✭
An INDEX/MATCH has a specific format. The formula above is not written in the expected format. The IFERROR is masking the error as you have it written to produce a blank field when errors are encountered.
Try this
=INDEX(COLLECT({PVSYST Forecast}, {Project Name},[Project Name]@row,{Month},[email protected]),1)
Kelly
Answers
-
Kelly Moore ✭✭✭✭✭✭
An INDEX/MATCH has a specific format. The formula above is not written in the expected format. The IFERROR is masking the error as you have it written to produce a blank field when errors are encountered.
Try this
=INDEX(COLLECT({PVSYST Forecast}, {Project Name},[Project Name]@row,{Month},[email protected]),1)
Kelly
-
I had to make a small adjustment, but I got it to work. You're a lifesaver!!! Thank you! Do you have suggestions on a place I get learn to get better with formulas?@Kelly Moore
Help Article Resources
Categories
=IFERROR(DATE(YEAR([Date Completed]@row) + 3, MONTH([Date Completed]@row), DAY([Date Completed]@row)), \"//www.santa-greenland.com/community/discussion/105991/\")<\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":324,"urlcode":"community-platform","name":"Community Platform"},{"tagID":485,"urlcode":"community-champions","name":"Community Champions"}]},{"discussionID":106206,"type":"question","name":"Pie Chart breaks when values are zero","excerpt":"In a dashboard, pie charts are breaks the reference and shows as Unable to display chart when reference values are changed to zero as these vales calculated via formula. Only Bar charts are showing as No data to display. My end goal is to show as No data to display when values are zero. Kindly help me out here.","categoryID":322,"dateInserted":"2023-06-08T15:21:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-08T18:01:33+00:00","insertUserID":162199,"insertUser":{"userID":162199,"name":"Gajapathi R M","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Gajapathi%20R%20M","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T17:37:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-08T18:05:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":24,"score":null,"hot":3372488578,"url":"https:\/\/community.smartsheet.com\/discussion\/106206\/pie-chart-breaks-when-values-are-zero","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106206\/pie-chart-breaks-when-values-are-zero","format":"Rich","tagIDs":[292],"lastPost":{"discussionID":106206,"commentID":379679,"name":"Re: Pie Chart breaks when values are zero","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379679#Comment_379679","dateInserted":"2023-06-08T18:01:33+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-08T18:05:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/7KSJLJBZG3GE\/only-bar-chart-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Only Bar Chart.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-08T17:37:29+00:00","dateAnswered":"2023-06-08T17:12:16+00:00","acceptedAnswers":[{"commentID":379665,"body":"
So is it a sheet that has the calculations on it or a row report? If it is a sheet then you can add 0.00001 to all calculations and decrease the shown decimals. This will maintain some data greater than zero but only display zero.<\/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":292,"urlcode":"Dashboards","name":"Dashboards"}]},{"discussionID":106211,"type":"question","name":"SUMIFS and CONTAINS or MAX","excerpt":"Here's a fun one that's got me scratching my head. So I am trying to add the rows that contain the terms Agile Kanban and waterfall. SUM works, =SUMIF((Variable$2:Variable$9), HAS((Variable$2:Variable$9), (Variable@row)), (A$2:A$9)) returns a 0 and =SUMIF(Variable$2:Variable$9, CONTAINS(Variable@row,…","categoryID":322,"dateInserted":"2023-06-08T16:00:30+00:00","dateUpdated":"2023-06-08T16:04:20+00:00","dateLastComment":"2023-06-08T16:54:43+00:00","insertUserID":160886,"insertUser":{"userID":160886,"name":"Nicole V","title":"Financial Projects Manager","url":"https:\/\/community.smartsheet.com\/profile\/Nicole%20V","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!eiPlp6_hG7I!7GDve_5PRfo!yeqJFAgg3ow","dateLastActive":"2023-06-08T16:37:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":15741,"lastUser":{"userID":15741,"name":"ker9","url":"https:\/\/community.smartsheet.com\/profile\/ker9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T18:55:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":20,"score":null,"hot":3372485713,"url":"https:\/\/community.smartsheet.com\/discussion\/106211\/sumifs-and-contains-or-max","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106211\/sumifs-and-contains-or-max","format":"Rich","lastPost":{"discussionID":106211,"commentID":379659,"name":"Re: SUMIFS and CONTAINS or MAX","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379659#Comment_379659","dateInserted":"2023-06-08T16:54:43+00:00","insertUserID":15741,"insertUser":{"userID":15741,"name":"ker9","url":"https:\/\/community.smartsheet.com\/profile\/ker9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T18:55:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/3BDTJOPMXHWQ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-08T16:49:15+00:00","dateAnswered":"2023-06-08T16:36:32+00:00","acceptedAnswers":[{"commentID":379652,"body":"
