# Ignore Blank cells in IF statement while matching columns

1. ## Ignore Blank cells in IF statement while matching columns

Hi Seniors/Gurus,

I am matching 6 column data at row level. I want to ignore blank cells keeping the formula consistent.

Regards  Register To Reply

2. ## Re: Ignore Blank cells in IF statement while matching columns  Register To Reply

3. ## Re: Ignore Blank cells in IF statement while matching columns

I tried this and it seemed to work...
=OR(IF(A3="","TRUE"),IF(AND(A3=B3,A3=C3,A3=D3,A3=E3,A3=F3),"TRUE","FALSE"))

EDIT, hold the horses, it didn't work for your values in row 4, will have to tinker with it more.   Register To Reply

4. ## Re: Ignore Blank cells in IF statement while matching columns

OK, now it did work in all instances, go ahead and give that formula a try and let me know.
not sure why it didn't work for row 4 at first but now it does.   Register To Reply

5. ## Re: Ignore Blank cells in IF statement while matching columns

hi pareshvm. maybe:
=COUNTIF(A2:F2,LOOKUP(2,1/(A2:F2<>""),A2:F2))=COUNTA(A2:F2)

@Sambo kid:
TRUE and FALSE are recognized in formulas, so you don't need double quotes.
=OR(IF(A3="",TRUE),IF(AND(A3=B3,A3=C3,A3=D3,A3=E3,A3=F3),TRUE,FALSE))

logical tests returns TRUE or FALSE, so you don't need IFs to do that. if A3 is "", it returns TRUE without needing the IF. you can try typing that somewhere
=A3=""

so just:
=OR(A2="",AND(A2=B2,A2=C2,A2=D2,A2=E2,A2=F2))

but i think the blanks may not be in Column A only. so if column A is filled with xyz, column B is empty, and the rest are also filled with xyz, then the formula wouldn't work

wishing you both a happy new year~  Register To Reply

6. ## Re: Ignore Blank cells in IF statement while matching columns

Thanks Benishiryo, its working  Register To Reply