Forum Discussion
Comparison Help!
Hello- within columns B & D of the below screenshot, i'm trying to write a two formula's which illustrates these outcomes, based on lookups between A & C (Previous) and C & A (Current).
I've tried XLOOKUP which works to an extent, but the return array captures numbers which I don't want to show in the comparison column. Any help would be appreciated. Thank you!
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.
4 Replies
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.
