Find / Search / Countif etc taking into account blanks and error messages...

[ellison]

Having a mind blank!
What would be best way to work out where the info in Col C is / isn't contained in the info in Col B, i.e. to say "Found" or "Not Found" respectively.
But also take into account that
- it is not case dependent
- if Col C is blank, please return: "Blank Anyway"
- IF Col C is not found in Col B, please return "Not Found" (as opposed to an error message)

Row	header-1	header-2	WIP
2	ABCDEFGH	ABC	Found
3	ABCDEFGH	abc	Found
4	ABCDEFGH	DEF	Found
5	ABCDEFGH	ABCDEFGH	Found
6	ABCDEFGH		Blank Anyway
7	ABCDEFGH	BLAH	Not Found
8	ABCDEFGH		Blank Anyway
9	ABCDEFGH		Blank Anyway
10	ABCDEFGH	ZZZZZZ	Not Found

Actually, this isn't a mind blank - I just can't figure it out!

 

[SanjayGMusafir]

Check this -

Book4
	A	B	C	D	E
1	Row	header-1	header-2	WIP	Formula
2	2	ABCDEFGH	ABC	Found	Found
3	3	ABCDEFGH	abc	Found	Found
4	4	ABCDEFGH	DEF	Found	Found
5	5	ABCDEFGH	ABCDEFGH	Found	Found
6	6	ABCDEFGH		Blank Anyway	Blank Anyway
7	7	ABCDEFGH	BLAH	Not Found	Not Found
8	8	ABCDEFGH		Blank Anyway	Blank Anyway
9	9	ABCDEFGH		Blank Anyway	Blank Anyway
10	10	ABCDEFGH	ZZZZZZ	Not Found	Not Found
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IFS(C2="","Blank Anyway",IFERROR(SEARCH(C2,B2),0)>=1,"Found",TRUE,"Not Found")

 

[mumps]

Place this formula in C2 and copy down: =IF(C2="","Blank Anyway",IF(ISNUMBER(SEARCH(C2,B2)),"Found","Not Found"))

 

[ellison]

Blimey that's amazing, brilliant thank you!!!
(and no wonder I couldn't do it )

 

[SanjayGMusafir]

Glad to help you