If a range contains a specific value by column
This tutorial shows how to test if a range contains a specific value by column and return a specified value if the test is True or False through the use of an Excel formula or VBA
Example: If a range contains a specific value by column
|
This formula uses the Excel HLOOKUP and ISNA functions to check if range (C8:G8) contains a value that is equal to a value in cell C5. The Excel IF function is then used to identify if the formula returns an N/A error, therefore, meaning that the value doesn't exist in the range and thus return a "No" value. If the specified value exists in the range the Excel IF function will return a "Yes" value.
|
Sub If_a_range_contains_a_specific_value_by_column()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a range contains a specific value by column and then return a specified value
Result = Application.HLookup(ws.Range("C5"), ws.Range("C8:G8"), 1, False)
If IsError(Result) Then
Result = Application.HLookup(ws.Range("C5"), ws.Range("C8:G8"), 1, False)
If IsError(Result) Then
ws.Range("I8") = "No"
Else
ws.Range("I8") = "Yes"
End If
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("I8") in the VBA code.
Range to Test: Select the range that you want to search through for a specific value by changing the range reference ("C8:G8") in the VBA code.
Specific Value: Select the specific value that you want to test for by changing the value in cell ("C5").
Worksheet Selection: Select the worksheet which captures the range that you want to test if it contains a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a range contains a specific value the VBA code will return a value of "Yes". If a range does not contain a specific value the VBA code will return a value of "No". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output Range: Select the output range by changing the cell reference ("I8") in the VBA code.
Range to Test: Select the range that you want to search through for a specific value by changing the range reference ("C8:G8") in the VBA code.
Specific Value: Select the specific value that you want to test for by changing the value in cell ("C5").
Worksheet Selection: Select the worksheet which captures the range that you want to test if it contains a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a range contains a specific value the VBA code will return a value of "Yes". If a range does not contain a specific value the VBA code will return a value of "No". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Explanation about the formula used to check if a range contains a specific value by column
EXPLANATION
EXPLANATION
This tutorial shows how to test if a range contains a specific value by column and return a specified value if the formula tests true or false, by using an Excel formula and VBA.
This tutorial provides one Excel method that can be applied to test if a range contains a specific value by column and return a specified value by using an Excel IF, ISNA and HLOOKUP functions. In this example, if the range contains a value specified in cell C5 the formula will return a value of "Yes". If the range does not contain the value in cell C5 the formula will return a value of "No".
This tutorial provides one VBA method that can be applied to test if a range contains a specific value and return a specified value.
FORMULA
=IF(ISNA(HLOOKUP(lookup_value, range, row_index_number, range_lookup)), value_if_true, value_if_false)
=IF(ISNA(HLOOKUP(lookup_value, range, row_index_number, range_lookup)), value_if_true, value_if_false)
ARGUMENTS
lookup_value: The value you want to lookup in the first row from the selected range (range).
range: A range of cells from which to lookup the value.
row_index_number: A row number in the table from which to source the data.
range_lookup: A choice of TRUE or FALSE. Selecting TRUE will lookup an approximate match. Selecting FALSE will lookup an exact match.
value_if_true: Value to be returned if the range does not contains the specific value.
value_if_false: Value to be returned if the range contains the specific value.
lookup_value: The value you want to lookup in the first row from the selected range (range).
range: A range of cells from which to lookup the value.
row_index_number: A row number in the table from which to source the data.
range_lookup: A choice of TRUE or FALSE. Selecting TRUE will lookup an approximate match. Selecting FALSE will lookup an exact match.
value_if_true: Value to be returned if the range does not contains the specific value.
value_if_false: Value to be returned if the range contains the specific value.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a range contains a specific value by row | How to test if a range contains a specific value by row and return a specified value using Excel and VBA methods | |
| If a range contains a value less than | How to test if a range contains a value less than a specific value and return a specified value using Excel and VBA methods | |
| If a range contains a value greater than | How to test if a range contains a value greater than a specific value and return a specified value using Excel and VBA methods | |
| If a range contains a value greater than or equal to | How to test if a range contains a value greater than or equal to a specific value and return a specified value using Excel and VBA methods | |
| If a range does not contain a specific value | How to test if a range does not contain a specific value and return a specified value using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| 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 | |
| HLOOKUP Function | The Excel HLOOKUP function searches for a specific value in the first row of the selected range (table) and returns a value that resides in the same column as the lookup value from a specific row |