In Excel, I have three formulas/statements I'd like to merge into one. I've listed the statements below. Is there a way to merge all three formulas into one? I'd like to learn how to write the logic to do this. Thanks.
=IF(COUNTIF($B:$B,$A2)=1, "MATCH")
=IF(COUNTIF($B:$B,$A2)>1, "DUPLICATE")
=IF(COUNTIF($B:$B,$A2)<1, "NO MATCH")
The syntax of the IF function is IF(true-false-condition, value-if-condition-true, value-if-condition-false) (where either missing value-if defaults to 0).
3-way (or more) IF conditionals can be written by nesting multiple boolean IF's, for example:
=IF(COUNTIF($B:$B,$A2)>1, "DUPLICATE", IF(COUNTIF($B:$B,$A2)=1, "MATCH", "NO MATCH"))
Also consider good practice of trapping the default 'else' case. The above answer does not do this, and it assumes your input will always be one of your stated cases (=1, <1 or >1) so omits the final IF condition. In that example this will not error because the evaluations will always return true or false, but this may not behave as expected if an input is a different data type, null set, etc. In the stated answer, this unexpected behaviour will "fail silently" which is a potential issue.
Instead, try:
=IF(COUNTIF($B:$B,$A2)>1, "DUPLICATE", IF(COUNTIF($B:$B,$A2)=1, "MATCH", IF(COUNTIF($B:$B,$A2)<1, "NO MATCH", "ERROR")))
In implementation you would replace "ERROR" with your chosen error handling method.
You could also look at the following Excel functions, as an alternative to nesting multiple IF functions:
SWITCH:
=SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
IFS:
=IFS (test1, value1, [test2, value2], ...)
Some of these are newer than others so be sure to test to your version of Excel (and consider compatibility with any other possible users of your file).