Skip to main content
Solved

Checking values match where one cell may be empty

  • April 21, 2021
  • 2 replies
  • 23 views
L

Hi!

I am building some QA into my Base, and have x4 date columns that I want to check all equal each other.

To ensure the first x3 dates equal each other I have used:

IF(AND({Date 1}={Date 2},{Date 2}={Date 3}),“Match”,“Not match”)

I think that is right and seems to work well.

This all works fine, but the issue I have is that the {Date 4} column doesn’t always have a value (intentionally), and hence I want to ignore it in the date comparison if not populated.

i.e. if {date 1}={date 2}={date 3} but {date 4} is empty, the formula should still return “Match” to signify that all dates match.

I’m sure this is very simple, but I’ve tried multiple different IF statements and quite simply can’t get it to work!

Any help would be greatly appreciated :slightly_smiling_face:
Thanks.

Best answer by Rose_K

Hello @Luke_Branford1!

I believe the following should do the trick:

IF(NOT({Date 4}),  
    IF(
        AND({Date 1}={Date 2},{Date 2}={Date 3}),
        "Match",
        "Not match"    
    ),
    IF(
        AND({Date 1}={Date 2},{Date 2}={Date 3}, {Date 3} = {Date 4}),
        "Match",
        "Not match"    
    )
)

Let me know if I can help provide any more information, and if this helped answer your question please mark it as the solution :white_check_mark: .

2 replies

R
Forum|alt.badge.img+4
  • Rose_K
  • Inspiring
  • Answer
  • April 22, 2021

Hello @Luke_Branford1!

I believe the following should do the trick:

IF(NOT({Date 4}),  
    IF(
        AND({Date 1}={Date 2},{Date 2}={Date 3}),
        "Match",
        "Not match"    
    ),
    IF(
        AND({Date 1}={Date 2},{Date 2}={Date 3}, {Date 3} = {Date 4}),
        "Match",
        "Not match"    
    )
)

Let me know if I can help provide any more information, and if this helped answer your question please mark it as the solution :white_check_mark: .

L
  • Luke_Branford1
  • Author
  • New Participant
  • April 23, 2021

@Rose_K This worked like a charm, thank you so much, hugely appreciated :slightly_smiling_face:

R
Terms & ConditionsAccessibility statement