Excel multiple conditional check including contains

excel if statement multiple conditions range
multiple if statements in excel with text
if function with 3 conditions
excel if between multiple ranges
excel if function multiple conditions
if function with 3 logical tests
if(and statement excel)
how to put condition in excel

Having trouble figuring out how to piece this together, not to mention coming up with a descriptive title for this question.

Psuedocode of what I'm trying to do in a cell is:

if ((A1 == 1 && B1.contains("abc")) || (A1 == 2 && B1.contains("def") || (A1 == 3 && B1.contains("ghi"))
then write("ok")
else write("not ok")

This will be case-sensitive. For case-insensitive, use SEARCH instead of FIND.

=IF(OR(AND(A1=1,IFERROR(FIND("abc",B1)>0,FALSE)),AND(A1=2,IFERROR(FIND("def",B1)>0,FALSE)),AND(A1=3,IFERROR(FIND("ghi",B1)>0,FALSE))),"ok","not ok")


=IF(IFERROR(FIND(INDEX({"abc","def","ghi"},$A$1),$B$1),FALSE),"ok","not ok")

Excel IF statement with multiple AND/OR conditions, nested IF , If your logical test contains the AND function, Microsoft Excel returns to start with the most important condition and make your functions as  Excel provides the IF function to perform conditional analyses. You can also nest IF functions — that is, use another IF function as an argument to the first IF function — when you need to check more than one condition. In this example, the first IF checks the value of E2.

You can try:

=IF(IFERROR(MATCH(A1,{1,2,3},0)=MATCH(TRUE,ISNUMBER(SEARCH({"abc","def","ghi"},B1)),0),FALSE),"OK","NOT OK")

As an array formula, which must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

If your values in Column A are actually numeric 1,2,3,... then you can simplify the formula to:


Checking for Multiple Conditions in Excel, The value_if_true and value_if_false arguments can contain simple conditions of their own. This allows you test more than one condition where subsequent conditions The figure shows a spreadsheet with two user input fields for the type of  The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel based on AND as well as OR logic. You will find a number of examples for different data types - numbers, dates, text, wildcard characters, non-blank cells and more. Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because

Here's the outline:

=IF(OR(AND({conditions}),AND({conditions}),AND({conditions})),"OK","NOT OK")

Does cell contain text? https://exceljet.net/formula/cell-contains-specific-text

IF with AND function – multiple conditions, The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. The IF function above checks two conditions, the "Region" value must Download excel *.xlsx If cell contains text from list If"different places" then will different number with multiple condition. Here you can check multiple conditions where you can use Excel If statement. 1) Excel If Statement If you want to test a condition to get two outcomes then you can use this Excel If statement. =If(Marks>=40, “Pass”)

How to use the Excel AND function, How to use the Excel AND function to Test multiple conditions with AND. logical2, etc.) must return TRUE or FALSE, or be arrays or references that contain logical values. In the example shown, the formula used for conditional formatting is:  After installing Kutools for Excel, please do as follows: 1. Click Enterprise > Super Filter, see screenshot: 2. In the Super Filter pane, please do the following operations: (1.) Check Specified option, and then click button to select the data range that you want to filter; (2.) Choose the relationship among the filter criteria as you need; (3.)

How to use the Excel OR function, How to use the Excel OR function to Test multiple conditions with OR. logical1 - The first condition or logical value to evaluate. logical2 - [optional] The must evaluate to TRUE or FALSE, or be arrays or references that contain logical values. In Excel 2007 – 2016, total 64 conditions can be nested up while working with multiple ifs. You have to maintain a proper order while working with multiple ifs. If your formula contains too many ifs, it’s better to use OR and AND functions with that.

How to apply conditional formatting search for multiple words in Excel?, Conditional formatting to highlight the cells contains one of several values Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more​. In fact, the Conditional Formatting in Excel can help you to highlight the cells if contain partial text as quickly as you need. Please do as follows: 1. Select the text list that you want to highlight the cells which contain partial text, and then click Home > Conditional Formatting > New Rule, see screenshot: 2.

  • Something tells me the 1,2,3 are simplifications of the actual inputs. If not then the second is a good formula.
  • @ScottCraner In my real case, the inputs happen to actually be 1 through 6, but your point is spot on.
  • I'm going to accept this because I was able to take the first sample and expand it out as I needed. I admire you guys who can think in a single line of code. I had to add line breaks and spacing to understand what the pieces were.