screen only the first 4 letters of a word and compare to a list of bad first letters

[namename]

I need help understanding how to screen names
I have tried many other formulas however it was not specific enough.

I am looking for formula that will screen only the first 4 letters of a word and compare to a list of bad first letters (A57 List). When it screens the name it will flag as "FAIL" as it has not passed the screening process.

I have tried IF statements, searching and many different formulas. The all end up screening the whole word and I just need the first 4 letters to not match.

Thank you!

*Update*
Used:

 =IF(ISNA(MATCH(LEFT(C3,4),Y2:Y5,0)),"Ok","FAIL")

When comparing to the full A57 List it was flagging words that should not have failed as "FAIL".
This may be issues with case sensitivity. I need it not to be case sensitive because multiple people will be inputting information into the file.

When using MATCH working on desktop excel however did not on Online Excel

 

[Fluff]

That formula is not case sensitive.
Can you post a sample of your data using the XL2BB add-in that shows where it's failing.

 

[DanteAmor]

Hi and welcome to MrExcel

Try this:

Libro1
	C	D																					Y
2																							scrae
3	screen	OK																					rainbow
4																							world
5																							SCRExd
Hoja4

Cell Formulas
Range	Formula
D3	D3	=IFERROR(IF(MATCH(LEFT(C3,4)&"*",Y2:Y5,0)>0,"OK"),"FAIL")

 

[Fluff]

Something that could cause the formula to give the wrong result, is if the values in col Y are not exactly 4 characters in length

 

[namename]

Here is a screenshoot, I wasn't able to get XL2BB working

 

[namename]

Something that could cause the formula to give the wrong result, is if the values in col Y are not exactly 4 characters in length

You think I should weed out all the letters less than 4, on my list I extracted the first 4 letters however some words does not have 4 letters

 

[Fluff]

The formula works for me, for the 4character "words"

Book(1).xlsx
	A	B	C											N	O	P								X	Y
1		Naming Process																							Artical 57 List
2		Name	Naming											NN	A57										A 31
3			welcome												OK										A L
4			ehnd												OK										A R
5			abdo												FAIL										A.T.
6			history												FAIL										A.Vo
7			Acov												FAIL										Aaa
8			ACOV												FAIL										Aaci
9			acover												FAIL										Aafa
10			abbacity												FAIL										Aaja
11			ABBAcity												FAIL										Aara
12			a 31567												FAIL										Aass
13			a.t.123												FAIL										Abac
14																									Abak
15																									Abal
16																									Aban
Sheet1

Cell Formulas
Range	Formula
O3:O13	O3	=IF(ISNA(MATCH(LEFT(C3,4),Y:Y,0)),"OK","FAIL")

 

[namename]

Do you think it can be affected by the list because there is over 11000 words to screen from

 

[Fluff]

That should not make any difference (other than time).

 

[Fluff]

Another option would be to setup a range name using this formula
=OFFSET(Sheet1!$Y$2,,,COUNTA(Sheet1!$Y:$Y)-1)
and then you could use this formula in the sheet
=IF(SUM(COUNTIF(C3,FailList&"*")),"FAIL","Ok")

where FailList is the range name

Book(1).xlsx
	A	B	C										M	N	O	P								X	Y
1		Naming Process																							Artical 57 List
2		Name	Naming										N#	NN	A57										A 31
3			welcome											Ok	OK										A L
4			ehnd											Ok	OK										A R
5			abdo											FAIL	FAIL										A.T.
6			history											FAIL	FAIL										A.Vo
7			Acov											FAIL	FAIL										Aaa
8			ACOV											FAIL	FAIL										Aaci
9			acover											FAIL	FAIL										Aafa
10			abbacity											FAIL	FAIL										Aaja
11			ABBAcity											FAIL	FAIL										Aara
12			a 31567											FAIL	FAIL										Aass
13			a.t.123											FAIL	FAIL										Abac
14			abedfg											FAIL	OK										Abak
15																									Abal
16																									Aban
Sheet1

Cell Formulas
Range	Formula
N3:N14	N3	=IF(SUM(COUNTIF(C3,FailList&"*")),"FAIL","Ok")
O3:O5,O7:O13	O3	=IF(AND(ISNA(MATCH(LEFT(C3,4),Y:Y,0)),ISNA(MATCH(LEFT(C3,3),Y:Y,0))),"OK","FAIL")
O6,O14	O6	=IF((ISNA(MATCH(LEFT(C6,4),Y:Y,0))),"OK","FAIL")

Named Ranges
Name	Refers To	Cells
FailList	=OFFSET(Sheet1!$Y$2,,,COUNTA(Sheet1!$Y:$Y)-1)	N3:O14