IF AND Formula with ISNUMBER

[Chialiwa]

I am writing a formula to categorize items based on item number but want any item that contains "IQF" in the description to have a different category. Anything containing the same item number range plus IQF in the description should say "IQF Vegetables". Here is the formula I'm using:

=IFS(AND(A2>199999,A2<300000),"Vegetables",AND(A2>199999,A2<300000,ISNUMBER(SEARCH("IQF",B2))),"IQF Vegetables")

Hoping this is something simple I'm overlooking. Thank you!

 

[dreid1011]

It seems it would work better if you put the AND() with more conditions first, since the formula as it is stops at the first TRUE result and both match the first set of conditions before it even gets to looking for "IQF".

Test Sheet Summary.xlsx
	A	B	C
1	Item Number	Item Description	Category
2	200002	OnionFirRstd3/8"DiceIQF,Org,PL	IQF Vegetables
3	200009	Raisins,Thompson,Organic KSA	Vegetables
Sheet2

Cell Formulas
Range	Formula
C2:C3	C2	=IFS(AND(A2>199999,A2<300000,ISNUMBER(SEARCH("IQF",B2))),"IQF Vegetables",AND(A2>199999,A2<300000),"Vegetables")

 

[Scott Huish]

You only seem to be returning Vegetables or IQF Vegetables so it seems to me all you have to look for is IQF
=IF(ISNUMBER(SEARCH("IQF",B3)),"IQF ","")&"Vegetables"

 

[Chialiwa]

You only seem to be returning Vegetables or IQF Vegetables so it seems to me all you have to look for is IQF
=IF(ISNUMBER(SEARCH("IQF",B3)),"IQF ","")&"Vegetables"

Sorry, no, the string is huge and I'm returning many other things but didnt want to clog the thread with a huge formula. I apologize, I should have specified that. THe full formula is:

=IFS(AND(A2857>-1,A2857<79999),"FG",AND(A2857>80000,A2857<90000),"DTH",AND(A2857>99999,A2857<200000),"Dairy",AND(A2857>199999,A2857<300000),"Vegetables",AND(A2857>199999,A2857<300000,ISNUMBER(SEARCH("IQF",B2857))),"IQF Vegetables",AND(A2857>299999,A2857<400000),"Grains",AND(A2857>399999,A2857<500000),"Oils & Liquids",AND(A2857>499999,A2857<600000),"Spices",AND(A2857>599999,A2857<700000),"INT",AND(A2857>699999,A2857<800000),"PKG",A2857="000811F","FG")

 

[dreid1011]

Full formula with the two Vegetable conditions swapped:

=IFS(AND(A2857>-1,A2857<79999),"FG",AND(A2857>80000,A2857<90000),"DTH",AND(A2857>99999,A2857<200000),"Dairy",AND(A2857>199999,A2857<300000,ISNUMBER(SEARCH("IQF",B2857))),"IQF Vegetables",AND(A2857>199999,A2857<300000),"Vegetables",AND(A2857>299999,A2857<400000),"Grains",AND(A2857>399999,A2857<500000),"Oils & Liquids",AND(A2857>499999,A2857<600000),"Spices",AND(A2857>599999,A2857<700000),"INT",AND(A2857>699999,A2857<800000),"PKG",A2857="000811F","FG")

 

[Chialiwa]

It seems it would work better if you put the AND() with more conditions first, since the formula as it is stops at the first TRUE result and both match the first set of conditions before it even gets to looking for "IQF".

Test Sheet Summary.xlsx
	A	B	C
1	Item Number	Item Description	Category
2	200002	OnionFirRstd3/8"DiceIQF,Org,PL	IQF Vegetables
3	200009	Raisins,Thompson,Organic KSA	Vegetables
Sheet2

Cell Formulas
Range	Formula
C2:C3	C2	=IFS(AND(A2>199999,A2<300000,ISNUMBER(SEARCH("IQF",B2))),"IQF Vegetables",AND(A2>199999,A2<300000),"Vegetables")

This worked!!!!!! Thank you SO much!!!!!!!

 

[dreid1011]

You're welcome, and happy to help.