Comparing 2 cells for a partial much but need to ignore cells that are blank

[ctucker]

I am using the following formulas to compare 2 cells for a partial match. The problem is, some of the cells are blank and it causes it to show up as a partial match.

Is there a way to compare 2 cells for a partial match that prevents blank cells showing up as a partial match?

Column E =IF(COUNTIF(D2,"*" & B2 & "*"),"Found match","No match")
Column F =IF(COUNTIF(D2,"*" & C2 & "*"),"Found match","No match")


B C D E F

		HBO00	Found match	Found match
		000073	Found match	Found match
AI	XY	ACA11,AID03	Found match	No match

 

[DanteAmor]

Hi and welcome to MrExcel!

Try:

=IF(B2="","",IF(COUNTIF(D2,"*" & B2 & "*"),"Found match","No match"))

=IF(C2="","",IF(COUNTIF(D2,"*" & C2 & "*"),"Found match","No match"))

 

[dreid1011]

Welcome to the board. You could do something like this and check B2/C2 for blanks and purposely return a string you know won't match anything:

Edit: Or use Dante's solution which will return a blank result if B2/C2 are blank, instead of "No Match".

Book1 1-18-2024.xlsm
	A	B	C	D	E	F
1
2				HBO00	No match	No match
3				000073	No match	No match
4		AI	XY	ACA11,AID03	Found match	No match
Sheet5

Cell Formulas
Range	Formula
E2:E4	E2	=IF(COUNTIF(D2,"*" & IF(B2="","BLANK",B2) & "*"),"Found match","No match")
F2:F4	F2	=IF(COUNTIF(D2,"*" & IF(C2="","BLANK",C2) & "*"),"Found match","No match")

 

[ctucker]

Welcome to the board. You could do something like this and check B2/C2 for blanks and purposely return a string you know won't match anything:

Edit: Or use Dante's solution which will return a blank result if B2/C2 are blank, instead of "No Match".

Book1 1-18-2024.xlsm
	A	B	C	D	E	F
1
2				HBO00	No match	No match
3				000073	No match	No match
4		AI	XY	ACA11,AID03	Found match	No match
Sheet5

Cell Formulas
Range	Formula
E2:E4	E2	=IF(COUNTIF(D2,"*" & IF(B2="","BLANK",B2) & "*"),"Found match","No match")
F2:F4	F2	=IF(COUNTIF(D2,"*" & IF(C2="","BLANK",C2) & "*"),"Found match","No match")

That was my next question. Thanks!

 

[ctucker]

Welcome to the board. You could do something like this and check B2/C2 for blanks and purposely return a string you know won't match anything:

Edit: Or use Dante's solution which will return a blank result if B2/C2 are blank, instead of "No Match".

Book1 1-18-2024.xlsm
	A	B	C	D	E	F
1
2				HBO00	No match	No match
3				000073	No match	No match
4		AI	XY	ACA11,AID03	Found match	No match
Sheet5

Cell Formulas
Range	Formula
E2:E4	E2	=IF(COUNTIF(D2,"*" & IF(B2="","BLANK",B2) & "*"),"Found match","No match")
F2:F4	F2	=IF(COUNTIF(D2,"*" & IF(C2="","BLANK",C2) & "*"),"Found match","No match")

Welcome to the board. You could do something like this and check B2/C2 for blanks and purposely return a string you know won't match anything:

Edit: Or use Dante's solution which will return a blank result if B2/C2 are blank, instead of "No Match".

Book1 1-18-2024.xlsm
	A	B	C	D	E	F
1
2				HBO00	No match	No match
3				000073	No match	No match
4		AI	XY	ACA11,AID03	Found match	No match
Sheet5

Cell Formulas
Range	Formula
E2:E4	E2	=IF(COUNTIF(D2,"*" & IF(B2="","BLANK",B2) & "*"),"Found match","No match")
F2:F4	F2	=IF(COUNTIF(D2,"*" & IF(C2="","BLANK",C2) & "*"),"Found match","No match")

Thanks! It worked great