More than one IF condition - does not work...?

[alexvlad12]

Hi Everyone,
I have this function:

=IF(SEARCH("*equal*";B5);"As per dsign"); IF(SEARCH("*crap*";B5);"works") which displays #VALUE! instead of what i want it to display

However this =IF(SEARCH("*equal*";B5);"As per dsign") works as it should.

What am I doing wrong?

Thanks for help,
Alex

[Jonmo1]

However this =IF(SEARCH("*equal*";B5);"As per dsign") works as it should.

Are you sure that works the way you think it does?
if B5 does NOT contain "equal", then that formula will return #Value!


Anyway, are you going to expand this to more than just those 2 strings?
If so, build a table to contain the strings and the results for each string..
Say E1:F2
E1:E2 = equal and crap
F1:F2 = As per dsign and works

Then use
=LOOKUP(2^15,SEARCH(E$1:E$2,B5),F$1:F$2)


There cannot be any blanks in E1:E2


Note, you'll need to change the , to ; (My machine uses , list seperator)

[Pete_UK]

Try it like this:

=IF(ISNUMBER(SEARCH("*equal*";B5));"As per dsign";IF(ISNUMBER(SEARCH("*crap*";B5));"works";"not_defined"))

Hope this helps.

Pete

[alexvlad12]

Hi guys,

@Jonmo1 - i need to extend this to more than 2 conditions

@Pete_UK - this displays 'not_defined' for some reason

Any ideas why?

Thanks,
Alex

[Pete_UK]

- this displays 'not_defined' for some reason

That means that neither of those two words can be found within B5.

Why don't you try to explain in words what it is that you want to do.

Pete

[Jonmo1]

Did you try my suggestion?


FYI, the use of the * as a wildcard is unnecessary here.
That's pretty much the purpose of Search.

[xwarlock10x]

I think i understand what you wrote.. .. Try this. =if(b5="equal","as per design",if(b5="crap","works",""))

hi everyone,
i have this function:

=if(search("*equal*";b5);"as per dsign"); if(search("*crap*";b5);"works") which displays #value! Instead of what i want it to display

however this =if(search("*equal*";b5);"as per dsign") works as it should.

What am i doing wrong?

Thanks for help,
alex

[alexvlad12]

Good morning

@Jonmo1 - i have a very specific excel file where i can't add anymore extra cells so i can't try your solution
@Pete - that is impossible, i have checked many times, the words are there.
@xwarlock10x - this does not work either, error in syntax apparently

So Pete i have a long list that looks like this:

Visual Pass Flash content found As per design
Visual Pass Check that the data collected is the minimal necessary (e.g. if subscribing to email list, don't ask for mobile number). #VALUE!

To the last column i apply the following formula:
=IF((SEARCH("*flash*";E250));"As per design";IF((SEARCH("*Check*";E250));"Verified";""))

and the results are as you see: first one works -> displays As per design and the 2nd doesnt, displays #VALUE!

What am I doing wrong?

Thank you
Alex

[Pete_UK]

As Jonmo stated in post #6, you don't need the asterisks with SEARCH, so try it like this:

=IF(ISNUMBER(SEARCH("flash";E250));"As per design";IF(ISNUMBER(SEARCH("Check";E250));"Verified";""))

Hope this helps.

Pete

[Jonmo1]

@Jonmo1 - i have a very specific excel file where i can't add anymore extra cells so i can't try your solution

That is a big problem then because of this

@Jonmo1 - i need to extend this to more than 2 conditions

If you're extending this out to convert multiple different "key words" to phrases, then the If(ISNUMBER(...) structre is going to get VERY cumbersome.
I'm surprised that your workbook is so demanding that it can't accomidate a simple lookup table.
You can even put it in a new sheet, and then HIDE that sheet.

[alexvlad12]

Thank you Everyone!

@Pete & Jonmo - that worked!

I'm going to try to add more conditions see, where this breaks.

SO, say I want to make this more complicated and say if WORD1 but NOT Word2 ... is this even possible?

Thank you,
Alex

[Pete_UK]

There isn't a BUT function in Excel, so you have to do it like this:

Please Login or Register  to view this content.

or slightly simpler:

Please Login or Register  to view this content.

Note that ISNUMBER is checking for the presence of word1, ISNA is checking for the absence of word2.

Hope this helps.

Pete

[Jonmo1]

ISNA wouldn't work because Search returns #Value! when the word isn't found...Not #N/A
So perhaps ISERROR instead..

[Pete_UK]

Thanks, Jonmo - I should have checked !!

Pete