Not working
I have a helper column named "location ~ 2023 - 01 january" and it combined a location and the FY reporting month if a site has reported. what i created was an IF(MATCH(location ~ 2023 - 01 january, matches the location one on the other sheet, 0), "Yes", "No"). meaning that if the locations matched then it would read yes if they did and no if they did not. i am unsure why but it worked for one site but not the rest. anyone know what is wrong?
Best Answer
-
The MATCH function outputs a numerical value. The one that it worked for was probably the first entry on the reference list, so it output a 1 which is also equal to "true". Try something more like IF the MATCH output is greater than zero then output "Yes", otherwise "No". I would also suggest working in an IFERROR to have the MATCH output zero instead of the error message when it is not found.
IF(IFERROR(MATCH(location ~ 2023 - 01 january, matches the location one on the other sheet, 0), 0) > 0, "Yes", "No")
Answers
-
The MATCH function outputs a numerical value. The one that it worked for was probably the first entry on the reference list, so it output a 1 which is also equal to "true". Try something more like IF the MATCH output is greater than zero then output "Yes", otherwise "No". I would also suggest working in an IFERROR to have the MATCH output zero instead of the error message when it is not found.
IF(IFERROR(MATCH(location ~ 2023 - 01 january, matches the location one on the other sheet, 0), 0) > 0, "Yes", "No")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!