need to look for text in cell in column and then return a phrase in a new cell vba or if

[damsel123]

look for 1) 3 clr,4 clr ,5 clr, 6 clr return 3-6 F
2) 8 clr,10 clr ,12 clr, return 8 -12 f
if not found return value in not found col

Description	not found
5 Clr GAM 4221 x 6452
4 Clr XYZ 1234 X 4561
6 Clr XYZ 9843 X 5678
10 Clr XYZ 4433 X 2211
12 Clr XYZ 7777 X 9999
4 Livingroom Clr 9876 x 5656	Bin a
5 Livingroom Clr 8876 x 6656	bin b

 

[DanteAmor]

Hi @damsel123
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

I recommend you put in a table the texts you want to search for and the phrase you want as a result. In my example, I put the table in columns G and H.

Review the following example to see if it meets what you need.

Dante Amor
	A	B	C	D	E	F	G	H
1	Description	not found					text	phrase
2	5 Clr GAM 4221 x 6452	return 3-6 F					3 clr	return 3-6 F
3	4 Clr XYZ 1234 X 4561	return 3-6 F					4 clr	return 3-6 F
4	6 Clr XYZ 9843 X 5678	return 3-6 F					5 clr	return 3-6 F
5	10 Clr XYZ 4433 X 2211	return 8 -12 f					6 clr	return 3-6 F
6	12 Clr XYZ 7777 X 9999	return 8 -12 f					8 clr	return 8 -12 f
7	4 Livingroom Clr 9876 x 5656	return 3-6 F					10 clr	return 8 -12 f
8	5 Livingroom Clr 8876 x 6656	return 3-6 F					12 clr	return 8 -12 f
9	XYZ 7777 X 9999	not found
10	7 Clr XYZ 7777 X 9999	not found
Hoja3

Cell Formulas
Range	Formula
B2:B10	B2	=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(SUBSTITUTE($G$2:$G$8," ","*"),A2)),$H$2:$H$8), "not found")

---------------------

Also try the following formula

Dante Amor
	A	B	C	D	E	F	G	H
1	Description	not found					text	phrase
2	5 Clr GAM 4221 x 6452	return 3-6 F					3 clr	return 3-6 F
3	4 Clr XYZ 1234 X 4561	return 3-6 F					4 clr	return 3-6 F
4	6 Clr XYZ 9843 X 5678	return 3-6 F					5 clr	return 3-6 F
5	10 Clr XYZ 4433 X 2211	return 8 -12 f					6 clr	return 3-6 F
6	12 Clr XYZ 7777 X 9999	return 8 -12 f					8 clr	return 8 -12 f
7	4 Livingroom Clr 9876 x 5656	not found					10 clr	return 8 -12 f
8	5 Livingroom Clr 8876 x 6656	not found					12 clr	return 8 -12 f
9	XYZ 7777 X 9999	not found
10	7 Clr XYZ 7777 X 9999	not found
Hoja3

Cell Formulas
Range	Formula
B2:B10	B2	=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($G$2:$G$8,A2)),$H$2:$H$8), "not found")

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​