Not working

Please help

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?

Paul Newcome

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")

Paul Newcome

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")