Formula help

[Isabella]

Hi,

I need a formula that will return "ERROR" if there is a string "UKN" in Col A3:A

Excel Workbook
	A
2	Header
3	Test1
4	Test2
5	Test3
6	Test4
7	Test5
8	Test6
9	UKN
10	UKN
11	Test7
12	Test8
13	Test9
14	Test10
15	Test11
Sheet1

 

[kpark91]

Put this in B2 and copy down

=IF(A2="UKN", "ERROR", A2)

 

[jasonb75]

I'm assuming you want 1 formula to check the whole range,

=if(countif(A3:A15,"UKN"),"Error","Ok")

 

[Robert Mika]

=IF(ISNUMBER(MATCH("UKN",A3:A10000,)),"Error","")

or

=IF(COUNTIF(A3:A10000,"UKN")>0,"Error","")

 

[Aladin Akyurek]

If you only need a single, diagnostic formula, saying al least one or none:

=ISNUMBER(MATCH("UKN",A:A,0))+0

Custom format the formula cell as, e.g.: [=0]"OK";[=1]"ERROR"

 

[Isabella]

If you only need a single, diagnostic formula, saying al least one or none:

=ISNUMBER(MATCH("UKN",A:A,0))+0

Custom format the formula cell as, e.g.: [=0]"OK";[=1]"ERROR"

Since this workbook will be opened by other teams would they see the error since i have only custom formatted in my puter?

 

[jasonb75]

Since this workbook will be opened by other teams would they see the error since i have only custom formatted in my puter?

The custom format is in the workbook, not your computer.