Forum Discussion

Copper Contributor

Mar 01, 2023

Solved

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 trie...

excel

Formulas and Functions

Riny_van_Eekelen
Mar 01, 2023
ElAir101
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.

elair101.xlsx10 KB

ElAir101

Copper Contributor

Mar 01, 2023

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?

Riny_van_Eekelen

Platinum Contributor

Mar 01, 2023

ElAir101

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.

elair101.xlsx10 KB

ElAir101
Copper Contributor
Mar 01, 2023
Thank you!