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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!