Excel formula help (multiple containing function)?

Question

Looking for a formula to search a cell for multiple things, e.g. check to see if A2 contains the letter X or the letter Y or the letter Z.

I found the below function which seems ok, but not sure how to incorporate the other letters (nested IF formula is complicated!). Maybe there is an easier way?

=IF(ISNUMBER(SEARCH("X",A2)), X, "Not OK") Checks to see if A2 contain the letter X (X)

Answer 1

Your formula is good with a little modification
=IF( ISNUMBER( SEARCH( "X",A2)), "X", "")&IF( ISNUMBER( SEARCH( "Y",A2)), "Y", "")&IF( ISNUMBER( SEARCH( "Z",A2)), "Z", "")
if A2 contains any instance of x,y,z, it will show its present.
Hope this helps.

Answer 2

Excel Contains Function

Answer 3

This Site Might Help You.

RE:
Excel formula help (multiple containing function)?
Looking for a formula to search a cell for multiple things, e.g. check to see if A2 contains the letter X or the letter Y or the letter Z.

I found the below function which seems ok, but not sure how to incorporate the other letters (nested IF formula is complicated!). Maybe there is an easier...

Answer 4

Hi,

You're on the right track with a nested IF function, and yes they can be confusing to write.

This is one of those times where Excel's built-in formula builder might be of service. Use Insert > Formula and then click on the IF function to activate the formula builder.

Click the little boxes to the right of the empty fields to select ranges on the worksheets, or just type in the fields.

You still need to think through the logic of the nesting and whether or not you want to make it even tougher with nested AND or OR restrictions.

-Jim Gordon
Microsoft Mac MVP

MVPs are independent and do not work for Microsoft
http://mvp.support.microsoft.com/

Answer 5

Reverse Phone Number Look Up Services

Answer 6

There's no easy way to use cell based functions in Excel. You just have to test each one individually, then put them all together.

If your cell has to contain one of a known list of values, then you can use a NAMED RANGE and one of the functions VLOOKUP, LOOKUP or MATCH to check what the cell contains.

Answer 7

if a formula < 0 I want the amount to be 100%. If it is great than 0 then I want the formula entered. Help!

Answer 8

here are a couple of good sites were you can find the info you are looking for.

http://www.ozgrid.com/Excel/excel-vlookup-formula.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html
http://office.microsoft.com/en-us/excel/HP052001271033.aspx