Formula to Find a String of Text and Return a Specific Value

[hkydad]

Hi,

I am trying to write an excel formula that reads through the Doc Code column "-sann", then I want it to return "A-BCD" or if it finds "-ann" then I want it to return "M-NOPQ". A small sample is below.

REX-SANN	A-BDE
TRE-ANN	M-NOPQ
JONNY-ANN	M-NOPQ

Thank you in advance for any assistance.

 

[HighAndWilder]

Hi,

I am trying to write an excel formula that reads through the Doc Code column "-sann", then I want it to return "A-BCD" or if it finds "-ann" then I want it to return "M-NOPQ". A small sample is below.

REX-SANN	A-BDE
TRE-ANN	M-NOPQ
JONNY-ANN	M-NOPQ

Thank you in advance for any assistance.

A small sample but does this work for you?

Book2.xlsx
	B	C	D	E	F	G
2
3		REX-SANN	A-BDE		A-BDE
4		TRE-ANN	M-NOPQ
5		JONNY-ANN	M-NOPQ
6
Sheet1

Cell Formulas
Range	Formula
F3	F3	=XLOOKUP("*ann*",C3:C5,D3:D5,"",2)

 

[Sufiyan97]

Try

Book2
	A	B	C
1	REX-SANN	A-BCDE
2	TRE-ANN	M-NOPQ
3	JONNY-ANN	M-NOPQ
4
5
6
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=IF(ISNUMBER(SEARCH("-Sann",A1)),"A-BCDE",IF(ISNUMBER(SEARCH("-ANN",A1)),"M-NOPQ",""))

 

[hkydad]

Thank you @ Sufuyan97. This is exactly what I was looking for to solve this problem. This solution will eliminate the need for typeing the codes in 1 by 1, espcially when there will be 300+ rows of data.

​

 

[Sufiyan97]

You're welcome!
Glad we could help!

 

[Scott Huish]

Without the nested IFs:
=XLOOKUP(TEXTAFTER(A1,"-",,,,""),{"SANN","ANN"},{"A-BCD","M-NOPQ"},"",0)