IF and OR Functions combined
Applying the IF and OR functions into a single formula will return a specified value if at least one of the conditions is TRUE or return another specified value is all of the conditions are FALSE
Example: Excel IF and OR Functions combined
|
The IF and OR formula tests if the value in cell B8 is equal to Team A or if the value in cell C8 is equal to win. If one of these tests are TRUE the formula will return OK, otherwise it will return CHECK in cell D8. To apply this formula across all values in the table, simply drag it down.
|
METHOD 2. IF and OR Functions combined using links
EXCEL
|
The IF and OR formula tests if the value in cell B8 is equal to the value in cell B5 or if the value in cell C8 is equal to the value in cell C5. If one of these tests are TRUE the formula will return OK, otherwise it will return CHECK in cell D8. To apply this formula across all values in the table, simply drag it down.
|
Sub IF_OR_Functions_Combined_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("IF and OR")
'apply the Excel IF and OR formula
If ws.Range("B8") = "Team A" Or ws.Range("C8") = "win" Then
If ws.Range("B8") = "Team A" Or ws.Range("C8") = "win" Then
ws.Range("D8") = "OK"
Else
ws.Range("D8") = "CHECK"
End If
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named IF and OR.
Worksheet Name: Have a worksheet named IF and OR.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D8") in the VBA code to any cell in the worksheet that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("D8") in the VBA code to any cell in the worksheet that doesn't conflict with the formula.
METHOD 2. IF and OR Functions combined using VBA with links
VBA
Sub IF_OR_Functions_Combined_Using_Links()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("IF and OR")
'apply the Excel IF and AND formula
If ws.Range("B8") = ws.Range("$B$5") Or ws.Range("C8") = ws.Range("$C$5") Then
If ws.Range("B8") = ws.Range("$B$5") Or ws.Range("C8") = ws.Range("$C$5") Then
ws.Range("D8") = "OK"
Else
ws.Range("D8") = "CHECK"
End If
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named IF and OR.
Worksheet Name: Have a worksheet named IF and OR.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D8") in the VBA code to any cell in the worksheet that doesn't conflict with the formula.
METHOD 3. IF and OR Functions combined using VBA with a For Loop
VBA
Sub IF_OR_Functions_Combined_Using_For_Loop()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("IF and OR")
'apply the Excel IF and AND formula
For x = 8 To 14
For x = 8 To 14
On Error Resume Next
If ws.Cells(x, 2) = ws.Range("$B$5") Or ws.Cells(x, 3) = ws.Range("$C$5") Then
ws.Cells(x, 4) = "OK"
Else
ws.Cells(x, 4) = "CHECK"
End If
Next
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named IF and OR.
Worksheet Name: Have a worksheet named IF and OR.
ADJUSTABLE PARAMETERS
Output Ranges: Select the output ranges by changing the For x values (8 to 14).
Output Range: Select the output column by changing the column number.
DESCRIPTION
Applying the IF and OR functions into a single formula will return a specified value if at least one of the conditions is TRUE or return another specified value is all of the conditions are FALSE.
Applying the IF and OR functions into a single formula will return a specified value if at least one of the conditions is TRUE or return another specified value is all of the conditions are FALSE.
FORMULA
=IF(OR(logical1, [logical2], ...), [value_if_true], [value_if_false])
=IF(OR(logical1, [logical2], ...), [value_if_true], [value_if_false])
ARGUMENTS
logical1: (Required) A condition that you want to test.
logical2: (Optional) A condition that you want to test.
[value_if_true]: (Optional) Return a value if the logic tests TRUE.
[value_if_false]: (Optional) Return a value if the logic tests FALSE.
logical1: (Required) A condition that you want to test.
logical2: (Optional) A condition that you want to test.
[value_if_true]: (Optional) Return a value if the logic tests TRUE.
[value_if_false]: (Optional) Return a value if the logic tests FALSE.
ADDITIONAL NOTES
Note 1: In Excel 2007 and later the OR function can accept up to 255 logical arguments. In Excel 2003 the OR function can only accept up to 30 logical arguments.
Note 2: The OR function ignores empty cells.