Forum Discussion

C R's avatar
C R
Copper Contributor
Oct 20, 2018

If Function

I am having problem with a very simple "IF" function. I am comparing two columns of data from different sources. It's basically IF( a2 = b2, true, false)  Even if the values are exactly the same, it only returns "false". What could be the problem? I've checked the formats for both fields and they are identical.

Need Help

2 Replies

Sort By
  • VitalieCiobanu's avatar
    VitalieCiobanu
    Copper Contributor
    Oct 21, 2018

    To add to what Detlef said, just to make sure your 2 cells have same value, use LEN to get the number of characters in your cells.

    See example below. Visually, A2 and B2 are same, but A2 has a space character in the end.

     

    =IF(A2 = B2,TRUE,FALSE) 
    a a
    =LEN(A2)=LEN(B2)

     

    LEN formula in A3 will show 2 in this case, while B3 will show 1, so definitely these cells are not identical.

    FALSE 
    a a
    21

     

    Removing the space char from A2, IF formula evaluates correctly.

    TRUE 
    aa
    11

     

    Also, you can use TRIM inside your IF formula, this will remove leading and trailing spaces from your referenced cell.

    =IF(TRIM(A2) = TRIM(B2),TRUE,FALSE) 
    a    a 
    =LEN(A2)=LEN(B2)

     

    Here is the result; even if I have that trailing space in A2, TRIM will take care of it.

    TRUE 
    a a
    21

     

    Before relying on TRIM 100%, I would recommend reading this article to know what TRIM removes and what it doesn't remove.

    https://support.office.com/en-us/article/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Oct 21, 2018

    Hi

     

    If A2=B2 equals FALSE then both values are definitely not identical.

    If it is text check for extra spaces.

    For numbers check ISNUMBER(A2) and ISNUMBER(B2). Both should be TRUE.

    And increase the number of decimal places.

     

     

Resources