Nested if

ariel20029

Board Regular
Joined
Jun 20, 2013
Messages
97
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
How about:

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

=IF(SUM(--ISNUMBER(SEARCH({"no charge","no-charge","no_charge","NCSO"},G2)))>0,"Check","")
 
Upvote 0
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
GHIJKL
1IncludeExclude
2No ChargeCheckNo*Nov
3No-ChargeCheckNCSONCC
4No_ChargeCheck
5NCSOCheck
6NSO 
7Nov 
8NCC 
9Thanks for your help, 
Hoja7
Cell Formulas
RangeFormula
H2:H9H2=IF((SUMPRODUCT(--ISNUMBER(SEARCH(include,G2)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,G2)))=0),"Check","")


Check more detail in this link:
 
Upvote 0

Forum statistics

Threads
1,223,970
Messages
6,175,721
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top