Nested if

[ariel20029]

Hi All,
In Column G I have customer PO's listed- When Order Entry types in the PO description each person has their own style. I need to identify when the PO field has the term No Charge.. They could type No Charge, NO_Charge, No-Charge, NCSO, NSO, nc..( and others but these are the main ones). When I try to type in a = IF(ISNUMBER(SEARCH("*No*",G2),"CHECK","")..
But this formula will pull in November too, When I try to Nest the if it does not like it.. probably a comma issue.. any suggestions?


G H
No Charge a Check
No-Charge b Check
No_Charge c Check
NCSO Check
NSO Check
Nov ( return Nothing)
NCC ( return Nothing)
Thanks for your help,
Sharon

 

[Scott Huish]

How about:

=IF(SUM(COUNTIF(G2,{"No?Charge*","NCSO*","NSO*"})),"Check","")

 

[DanteAmor]

How about

=IF(SUM(--ISNUMBER(SEARCH({"no charge","no-charge","no_charge","NCSO"},G2)))>0,"Check","")

 

[ariel20029]

Thank you all, I will try this first thing in the morning.

 

[DanteAmor]

Or you can put the words to include in one column and the words to exclude in another column.
You can name the ranges "include" and "exclude"

Book1
	G	H	I	J	K	L
1					Include	Exclude
2	No Charge	Check			No*	Nov
3	No-Charge	Check			NCSO	NCC
4	No_Charge	Check
5	NCSO	Check
6	NSO
7	Nov
8	NCC
9	Thanks for your help,
Hoja7

Cell Formulas
Range	Formula
H2:H9	H2	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(include,G2)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,G2)))=0),"Check","")

Check more detail in this link:

Cell contains one of many with exclusions

To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. In the example shown the formula in C5 is: =(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0) where...

exceljet.net

 

[ariel20029]

Thank you all, the formula worked perfectly

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.