IF Search formula only works for the first 2

[Jossilyns]

Hi All,

I am using the following formula in Excel Subscription product 365 to return a text value in the adjacent cell:

=IF(B11="","",
IF(SEARCH("FNQ",B11),"FNQ",
IF(SEARCH("NQ",B11),"NQ",
IF(SEARCH("FWQ",B11),"FWQ",
IF(SEARCH("CQ",B11),"CQ",
IF(SEARCH("WBB",B11),"WBB",
IF(SEARCH("SWQ",B11),"SWQ",
IF(SEARCH("SEQ",B11),"SEQ",""))))))))

It works for the first 2 returning Blank or FNQ but for all the other ones it returns #VALUE error.

Can someone please help?

 

[alansidman]

Your formula looks ok, however, if you expect some other input, then I suggest you post some sample data in which this formula is not working. Use XL2BB. If you need instructions, look at my signature. Do not post a picture as we cannot manipulate data in a picture.

 

[StephenCrump]

Welcome to the Forum!

Your #VALUE error is because the first SEARCH() will return #VALUE unless B11 contains "FNQ".

Try: =LET(s,{"FNQ","NQ","FWQ","CQ","WBB","SWQ","SEQ"},IFERROR(INDEX(s,MATCH(TRUE,ISNUMBER(SEARCH(s,B11)),)),""))

 

[Jossilyns]

WHS Training Register.xlsx
	A	B	C
3	Position	Location	Region
5	Frequency of training
6	Execution of training
7	Requirement:- Refer QBuild WHS Minimum Compentency Standard (PRO322)
8	Employee	Location
9	ALLEN, PETER	FNQ Thursday Island	FNQ
10	ANDERSON, GAVIN	NQ Garbutt (TSV office)	#VALUE!
11	ATKINSON, ALAN	FWQ Normanton	#VALUE!
12	BENNETT, TERRENCE (AGENCY)	FNQ Tolga/Atherton	FNQ
13	BLAIN, CHRISTOPHER	SWQ Roma	#VALUE!
14	BOWDITCH, BOBBY
15	BOYD, ROBERT	CQ Rockhampton	#VALUE!
FS WHS Training

Cell Formulas
Range	Formula
C9:C15	C9	=IF(B9="","", IF(SEARCH("FNQ",B9),"FNQ", IF(SEARCH("NQ",B9),"NQ", IF(SEARCH("FWQ",B9),"FWQ", IF(SEARCH("CQ",B9),"CQ", IF(SEARCH("WBB",B9),"WBB", IF(SEARCH("SWQ",B9),"SWQ", IF(SEARCH("SEQ",B9),"SEQ",""))))))))

Cells with Data Validation
Cell	Allow	Criteria
B9:B15	List	='Set up Info'!$B$9:$B$62

 

[Jossilyns]

Welcome to the Forum!

Your #VALUE error is because the first SEARCH() will return #VALUE unless B11 contains "FNQ".

Try: =LET(s,{"FNQ","NQ","FWQ","CQ","WBB","SWQ","SEQ"},IFERROR(INDEX(s,MATCH(TRUE,ISNUMBER(SEARCH(s,B11)),)),""))

That works! much appreciated!

 

[StephenCrump]

I was guessing FNQ etc made you a Queenslander!

If your data always follows this format, you could simplify to:

m
	A	B
1	FNQ Thursday island	FNQ
2	NQ Garbutt (TSV office)	NQ
3
4	SWQ Roma	SWQ
Sheet3

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR(LEFT(A1,FIND(" ",A1)-1),"")

 

[Phuoc]

Try this

Book1
	A	B	C
8	Employee	Location
9	ALLEN, PETER	FNQ Thursday Island	FNQ
10	ANDERSON, GAVIN	NQ Garbutt (TSV office)	NQ
11	ATKINSON, ALAN	FWQ Normanton	FWQ
12	BENNETT, TERRENCE (AGENCY)	FNQ Tolga/Atherton	FNQ
13	BLAIN, CHRISTOPHER	SWQ Roma	SWQ
14	BOWDITCH, BOBBY
15	BOYD, ROBERT	CQ Rockhampton	CQ
Sheet1

Cell Formulas
Range	Formula
C9:C15	C9	=LOOKUP(0,-SEARCH({"*","NQ","CQ","FNQ","FWQ","WBB","SWQ","SEQ"},B9&"*"),{"","NQ","CQ","FNQ","FWQ","WBB","SWQ","SEQ"})