Forum Discussion

Frequent Visitor

3 years ago

Solved

Compare 2 Columns, Return 3 values

Hi,

I want to compare two columns on the same table.

If the Columns match it should return 3.

If thet dont match return 2.

If both are blank return 1.

I want to use these values in conditional formatting. 1 for red, 2 for yellow and 3 for green.

I have tried this, but it wont return 1. Only returns 2 and 3.

Match = SWITCH(

TRUE(),

Table[Column1] = Table[Column2], 3,

ISBLANK(Table[Column1]) && ISBLANK(Table[Column2]), 1,

TRUE(), 2

)

Can someone please help, thanks.

SnymanGrobler
Try

Match =
SWITCH (
    TRUE (),
    Table[Column1] = BLANK ( ) && Table[Column2] = BLANK ( ), 1,
    Table[Column1] = Table[Column2], 3,
    2
)

or

Match =
SWITCH (
    TRUE (),
    Table[Column1] & Table[Column2] = "", 1,
    Table[Column1] = Table[Column2], 3,
    2
)

6 Replies

SnymanGrobler
Frequent Visitor
3 years ago
Greg_Deckler Please help
devanshi
Helper V
3 years ago
Match = IF(ISBLANK(Table[Column1]) && ISBLANK(Table[Column2]), 1,
IF(Table[Column1] = Table[Column2], 3, 2)
)
tamerj1
Community Champion
3 years ago
Hi SnymanGrobler
Please try

Match = SWITCH ( TRUE (), ISBLANK ( Table[Column1] ) && ISBLANK ( Table[Column2] ), 1, Table[Column1] = Table[Column2], 3, 2 )
- SnymanGrobler
  Frequent Visitor
  3 years ago
  Hi, tamerj1
  This does not work.
  My conditional formatting looks as follows:
  With this logic it still only returns green and yellow colors. Example:
  Do you have any idea why this is the case?
  Thanks
  - tamerj1
    Community Champion
    3 years ago
    SnymanGrobler
    Try
    
    Match = SWITCH ( TRUE (), Table[Column1] = BLANK ( ) && Table[Column2] = BLANK ( ), 1, Table[Column1] = Table[Column2], 3, 2 )
    
    or
    
    Match = SWITCH ( TRUE (), Table[Column1] & Table[Column2] = "", 1, Table[Column1] = Table[Column2], 3, 2 )

Related content