引用另一张表- VLOOKUP vs INDEX vs ??

<\/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":106279,"type":"question","name":"How do I create and If Formula pulling from another sheet to designate indicators?","excerpt":"Good Morning All, I am working on a Overall Health for a Project that looks like this: I need to update the lights by using an if statement looking at three different columns of another sheet that are not next to one another on the sheet, example of that sheet below In the first sheet for each Column I need to look at the…","categoryID":322,"dateInserted":"2023-06-09T16:40:18+00:00","dateUpdated":"2023-06-09T16:47:15+00:00","dateLastComment":"2023-06-11T19:20:33+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-11T21:34:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-12T03:09:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":57,"score":null,"hot":3372844251,"url":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","format":"Rich","lastPost":{"discussionID":106279,"commentID":380011,"name":"Re: How do I create and If Formula pulling from another sheet to designate indicators?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380011#Comment_380011","dateInserted":"2023-06-11T19:20: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-12T03:09:32+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\/XSK1SHR157VN\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-11T16:45:30+00:00","dateAnswered":"2023-06-11T12:13:51+00:00","acceptedAnswers":[{"commentID":380000,"body":"

If it isn't weekly, then you would use a COUNTIFS in nested IFs.<\/p>

=IF(COUNTIFS({Criteria}, @cell = \"Infrastructure\", {Due Date}, @cell<= TODAY())> 0, \"Red\", IF(.....<\/p>

<\/p>

You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.<\/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":106286,"type":"question","name":"Health Bubble Turn Colors when Status is Updated","excerpt":"Hello, I am new to Smartsheet. I am trying to get when I change my Status to \"Not Started\" I want the Health bubble to automatically turn grey. And when I change my Status to \"In Progress\" the Health bubble will turn yellow, etc. So far I have: IF(Status1 = \"Not Started\", \"Gray\", \" \"). I want it to change color for each…","categoryID":322,"dateInserted":"2023-06-09T19:29:22+00:00","dateUpdated":"2023-06-09T20:02:36+00:00","dateLastComment":"2023-06-09T21:14:52+00:00","insertUserID":162263,"insertUser":{"userID":162263,"name":"clairehunter","url":"https:\/\/community.smartsheet.com\/profile\/clairehunter","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-10T18:54:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162263,"lastUserID":150413,"lastUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":34,"score":null,"hot":3372684854,"url":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","format":"Rich","tagIDs":[254,440],"lastPost":{"discussionID":106286,"commentID":379933,"name":"Re: Health Bubble Turn Colors when Status is Updated","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379933#Comment_379933","dateInserted":"2023-06-09T21:14:52+00:00","insertUserID":150413,"insertUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-10T18:54:41+00:00","dateAnswered":"2023-06-09T21:14:52+00:00","acceptedAnswers":[{"commentID":379933,"body":"

You will need a nested IF formula:<\/p>

=IF(Status@row=\"Not Started\", \"Gray\", IF(Status@row=\"In Progress\", \"Yellow\", IF(Status@row=\"Complete\", \"Green\")))<\/p>

<\/p>

Just add more IF Statements like above for what you need. Put all ) at the very end. Then you can turn it into a column formula by right clicking the cell the formula is in and selecting column formula.<\/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"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">