Solved

# IF/OR Statement

Posted on 2011-02-15
283 Views
Just noticed my last ss shows any comments from AP, i only want the comments returned to AO if they are exactly as they are in the brackets. Otherwise "No Narrative Provided"

e.g. AO14 would be "No Narrative Provided" as well as A015

Thanks
Seamus
Example.xls
0
Question by:Seamus2626
• 3
• 3

LVL 85

Expert Comment

Can you clarify that? That appears to be the opposite of thhe intention of your formula, which is trying to return "No narrative provided" if the cell does match one of the items.
0

Author Comment

I know! Basically what im looking to achieve is:

If the text in AP matches any of the bracketed options in the IF Staement, return AP, If it doesnt exactly match the bracketed options or is blank, return "No Narrative Provided"

Thanks
Seamus
0

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
Then you need:

=IF(OR(TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Fidessa Booking instructed, Awaiting Sophis Booking",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Re - Allocated to different Source code",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="No Narrative Provided",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Open Investigation",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="fidessa booking to be made",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Corporate Actions Booking Adjustment required",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Paris involved in resolution",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="New break requires investigatation (1 - 3 day)"),TRIM(SUBSTITUTE(AP2,CHAR(160),"")), "No Narrative Provided")

because you have character 160 and trailing spaces in column AP (I guess you copied form the web)
0

Author Closing Comment

Thanks Rory!

Out of interest, what is Character 160?

Thanks
Seamus
0

LVL 85

Expert Comment

It's a non-breaking space, I believe.
0

Author Comment

Thank you
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…