How to use IF function with 3 arguments while ignoring blanks

Question

Okay so my question is how do i use IF with 3 arguments while one of the arguments is "Ignore blanks".

If A1 is "Escalation_complaint or "Escalation_request" B1 must show "Escalated". If A1 is any other text, B1 must show "Solved" If A1 is blank, B1 must remain empty.

Can someone please help me figure this out?

Answer 1

If you know that the text will always be Escalation_complaint or Escalation_request, you can do this with a nested IF query. =IF in Excel takes three parameters: what to verify, result if true, and result if false. You can have another IF statement in the what to do if it returns false section. We can also do an OR() statement to verify both Escalation_complaint and Escalation_request. The result looks like this:

=IF(OR(A1="Escalation_complaint",A1="Escalation_request"),"Escalated",IF(A1="","","Solved"))

The order here is important because you want the "for all other cases" to be in the final IF statement.

If you have a situation where you could also have "Request_escalation" or "complaint_escalation", you should make a more general solution that uses SEARCH or FIND which would allow you to do a more general lookup that allows for more values without having to hardcode them. Here is an example that makes it possible to find the word Escalation anywhere in the cell:

=IF(IFERROR(SEARCH("escalation",A1,1),0)>0, "Escalated", IF(A1="","","Solved"))

Answer 2

So, using two if(), and minimising the testing you need :

=IF(left(A1,3)="Esc","Escalated",IF(A1="","","Solved"))

Edit: Based on a comment to another answer, if testing for "esc" only could lead to problems then how about:

=IF(left(A1,5)="Escal","Escalated",IF(A1="","","Solved"))

Which will avoid words like "escaped" or "escargot"...

Answer 3

Your first test must be whether the A1 is empty or not. This must be the first because if you test whether an empty string is contained in another string the answer is always yes. =SEARCH("","Something") returns 1. So, How do you test for an empty cell? I recommend Excel's COUNTA() function but you may prefer testing for "". So, let's say, your first test is this.

=IF(COUNTA(A1), True, False)

Some would say IF(COUNTA(A1)>0, True, False) and that is equivalent. The simple IF(COUNTA(A1) tests for non-zero. Any number other than zero returns True.

So, now you got your basic function. What's supposed to happen if the result is True? What do you want to happen if the result is False? The latter is easy. If the result is False you want a null string returns. So, now your formula looks like this:-

=IF(COUNTA(A1), True, "")

Observe that we simply replaced the False with the desired output. So, what do you want to happen if A1 has something in it, if the first test resulted in True? There are two possibilities. That makes the problem solvable with a single IF. IF A1 has something starting with "Escalation" in it the result should be "escalate", else it should be "solved". So, how do you test for cell content?

=Find("escalation", A1) will not find "Escalation" (with a capital E). Therefore I recommend SEARCH(), which does.

=SEARCH("escalation", A1) should return 1. If it returns 1 your problem is solved because if it returns any other number its not "escalated" but "solved". Unfortunately, there is a third option. It might return an error. In fact it will return an error every time the word isn't found. That gives you a 3-way possibility (1, bigger than 1, or Error) which can't be solved with a single IF. So, I suggest to avoid the error.

=Find("escalation", A1 & "escalation") will find the word "escalation" every time. But it will find it in first position only if the conditions for "Escalate" is met. Therefore the formula for the True condition in the basic formula must be this:-

IF(SEARCH("escalation", A1 & "escalation") = 1, "Escalated", "Solved")

That's it. Assemble the second IF into the first and you're done.

Answer 4

Excel have maximum 30 arguments in a formula.

= if (A1=1, "True",if(B1>2,"True", "false"))

Understand this pattern. easy way. If you no longer understand this, or if your problem has not been resolved, attach a file or screenshot that relates to your problem.