Forum Discussion
Comparison Help!
Extend the formula so that it first checks if a cell is empty. The first formula could look like this:
=IF(A2:A8="","",IF(ISERROR(MATCH(A2:A8,C:C,0)),"Not in Current","Matched"))
It would work similar for the second one.
Obviously, it's better to work with structured tables. Than you avoid the direct cell references, as well as referencing entire columns. The principles are the same, although you can't use spilling formulas inside such tables. Good thing is that formulas inside structured tables replicate themselves when you add rows to the table. See attached and try for yourself.
In B2 use:
=IF(ISERROR(MATCH(A2:A4,C:C,0)),"Not in Current","Matched")And in D2 use:
=IF(ISERROR(MATCH(C2:C5,A:A,0)),"Not in Previous","Matched")
- Thank you- this works however if the formula is populated within an empty cell it returns 'Not in Current' (or previous) - in these instances, is there a way to just return a 0?
Extend the formula so that it first checks if a cell is empty. The first formula could look like this:
=IF(A2:A8="","",IF(ISERROR(MATCH(A2:A8,C:C,0)),"Not in Current","Matched"))
It would work similar for the second one.
Obviously, it's better to work with structured tables. Than you avoid the direct cell references, as well as referencing entire columns. The principles are the same, although you can't use spilling formulas inside such tables. Good thing is that formulas inside structured tables replicate themselves when you add rows to the table. See attached and try for yourself.
- Thank you!
