Excel IF Function

The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE

Example: Excel IF Function

Excel IF Function

METHOD 1. Excel IF Function using hardcoded values

EXCEL

=IF(B8="win","OK","CHECK")
Result in cell C8 (OK) - returns a value specified for a TRUE result, given the condition specified in the formula was met.

=IF(B9="win","OK","CHECK")
Result in cell C9 (CHECK) - returns a value specified for a FALSE result, given the condition specified in the formula was not met.

=IF(B10="win","OK","CHECK")
Result in cell C10 (CHECK) - returns a value specified for a FALSE result, given the condition specified in the formula was not met.

METHOD 2. Excel IF Function using links

EXCEL

=IF(B8=$B$5,"OK","CHECK")
Result in cell C8 (OK) - returns a value specified for a TRUE result, given the condition specified in the formula was met.

=IF(B9=$B$5,"OK","CHECK")
Result in cell C9 (CHECK) - returns a value specified for a FALSE result, given the condition specified in the formula was not met.

=IF(B10=$B$5,"OK","CHECK")
Result in cell C10 (CHECK) - returns a value specified for a FALSE result, given the condition specified in the formula was not met.

METHOD 3. Excel IF function using the Excel built-in function library with hardcoded values

EXCEL

Formulas tab > Function Library group > Logical > IF > populate the input boxes

=IF(B8="win","OK","CHECK")
Note: in this example we are populating all of the input boxes associated with the IF function. 		Built-in Excel IF Function using hardcoded values

METHOD 4. Excel IF function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > Logical > IF > populate the input boxes

=IF(B8=$B$5,"OK","CHECK")
Note: in this example we are populating all of the input boxes associated with the IF function. 		Built-in Excel IF Function using links

METHOD 1. Excel IF function using VBA with hardcoded values

VBA

Sub Excel_IF_Function_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("IF")
'apply the Excel IF function

If ws.Range("B8") = "win" Then

ws.Range("C8") = "OK"

Else

ws.Range("C8") = "CHECK"

End If

If ws.Range("B9") = "win" Then

ws.Range("C9") = "OK"

Else

ws.Range("C9") = "CHECK"

End If

If ws.Range("B10") = "win" Then

ws.Range("C10") = "OK"

Else

ws.Range("C10") = "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.
PREREQUISITES
Worksheet Name: Have a worksheet named IF.
Value to be Tested: Have the range of values that are to be tested captured in range ("B8:B10").

ADJUSTABLE PARAMETERS
Output Ranges: Select the output ranges by changing the cell references ("C8"), ("C9") and ("C10") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Value to be Tested: Select the range of values that are to be tested by changing the range ("B8:B10") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.

METHOD 2. Excel IF function using VBA with links

VBA

Sub Excel_IF_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("IF")
'apply the Excel IF function

If ws.Range("B8") = ws.Range("$B$5") Then

ws.Range("C8") = "OK"

Else

ws.Range("C8") = "CHECK"

End If

If ws.Range("B9") = ws.Range("$B$5") Then

ws.Range("C9") = "OK"

Else

ws.Range("C9") = "CHECK"

End If

If ws.Range("B10") = ws.Range("$B$5") Then

ws.Range("C10") = "OK"

Else

ws.Range("C10") = "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.
PREREQUISITES
Worksheet Name: Have a worksheet named IF.
Value to be Tested: Have the range of values that are to be tested captured in range ("B8:B10").
Value to be Tested against: This example only uses one value that is to be tested against, which is captured in cell ("B5"). Therefore, if using the exact same VBA code you need to ensure that cell ("B5") captures the value that you want to test against.

ADJUSTABLE PARAMETERS
Output Ranges: Select the output ranges by changing the cell references ("C8"), ("C9") and ("C10") in the VBA code to any cell in worksheet.
Value to be Tested: Select the range of values that are to be tested by changing the range ("B8:B10") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
Value to be Tested against: Select the cell that captures the value that is to be tested against by changing the cell reference ("B5") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.

METHOD 3. Excel IF function with a For Loop using VBA

VBA

Sub Excel_IF_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("IF")
'apply the Excel IF function

For x = 8 To 10
On Error Resume Next
If ws.Cells(x, 2) = ws.Range("$B$5") Then

ws.Cells(x, 3) = "OK"

Else

ws.Cells(x, 3) = "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.
PREREQUISITES
Worksheet Name: Have a worksheet named IF.
Value to be Tested: Have the range of values that are to be tested captured in range ("B8:B10").
Value to be Tested against: This example only uses one value that is to be tested against, which is captured in cell ("B5"). Therefore, if using the exact same VBA code you need to ensure that cell ("B5") captures the value that you want to test against.

ADJUSTABLE PARAMETERS
Output Ranges: Select the output ranges by changing the cell references ("C8"), ("C9") and ("C10") in the VBA code to any cell in worksheet.
Value to be Tested: Select the range of values that are to be tested by changing the range ("B8:B10") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
Value to be Tested against: Select the cell that captures the value that is to be tested against by changing the cell reference ("B5") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.

Usage of the Excel IF function and formula syntax

EXPLANATION

DESCRIPTION
The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE.
SYNTAX
=IF(logical_test, [value_if_true], [value_if_false])
ARGUMENTS
logical_test: (Required) 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.