Excel VBA: Check Whether an Array Contains a Value

VBA & macros · Updated November 21, 2026

To check whether a VBA array contains a value, loop through its elements and compare each one with the value you are looking for. For a one-dimensional array, a small Boolean function makes the check reusable; for a range or a simple lookup, VBA’s Application.Match can be a shorter alternative.

A reusable loop for a one-dimensional array

VBA does not provide a general-purpose Contains method for ordinary arrays. A loop is explicit, works with arrays created in VBA, and lets you decide how comparisons should behave. This function expects a one-dimensional array of comparable values:

Public Function ArrayContains(ByVal target As Variant, ByRef values As Variant) As Boolean
    Dim i As Long
    Dim firstIndex As Long
    Dim lastIndex As Long

    ' Return False if values is not an array or has no usable bounds.
    If Not IsArray(values) Then Exit Function

    On Error GoTo NoElements
    firstIndex = LBound(values, 1)
    lastIndex = UBound(values, 1)
    On Error GoTo 0

    ' Stop as soon as a matching element is found.
    For i = firstIndex To lastIndex
        If values(i) = target Then
            ArrayContains = True
            Exit Function
        End If
    Next i
    Exit Function

NoElements:
    Err.Clear
End Function

The function starts out False, which is the answer if no element matches. When it finds an equal value, it sets the return value to True and exits immediately. Reading the lower and upper bounds rather than assuming the array starts at zero matters: arrays can have different lower bounds, and some are declared with a starting index of 1.

Here is a complete caller. It creates a Variant array, checks for a value, and displays the result:

Public Sub CheckFruitArray()
    Dim fruits As Variant
    Dim found As Boolean

    ' Array creates a zero-based, one-dimensional Variant array.
    fruits = Array("Apple", "Pear", "Plum")
    found = ArrayContains("Pear", fruits)

    If found Then
        MsgBox "Pear is in the array."
    Else
        MsgBox "Pear is not in the array."
    End If
End Sub

Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even if a particular check only displays a message. In Excel for Microsoft 365 on Windows, Alt+F11 opens the Visual Basic Editor; on Mac, use Developer > Visual Basic or Option+F11 (some keyboards require Fn as well). Excel for the web does not run VBA macros.

What the comparison actually means

The expression values(i) = target uses VBA’s equality rules. For strings, the result depends on the module’s Option Compare setting: the default, Option Compare Binary, compares text case-sensitively, while Option Compare Text compares it without regard to case. If a module does not state an option, Binary comparison is the default. For example, under Binary comparison, “Pear” and “pear” are different strings.

If you want the function to make its case-insensitive behavior obvious without changing a module-level setting, use StrComp for text. This version is intended for strings, not mixed data types:

Public Function TextArrayContainsIgnoreCase(ByVal target As String, _
                                             ByRef values As Variant) As Boolean
    Dim i As Long
    Dim firstIndex As Long
    Dim lastIndex As Long

    If Not IsArray(values) Then Exit Function

    On Error GoTo NoElements
    firstIndex = LBound(values, 1)
    lastIndex = UBound(values, 1)
    On Error GoTo 0

    For i = firstIndex To lastIndex
        If StrComp(CStr(values(i)), target, vbTextCompare) = 0 Then
            TextArrayContainsIgnoreCase = True
            Exit Function
        End If
    Next i
    Exit Function

NoElements:
    Err.Clear
End Function

Do not use that text-specific version indiscriminately on arrays that may contain Excel error values, Nulls, or objects: converting those elements with CStr can fail. For mixed or potentially unusual data, handle those cases deliberately before comparing, or keep the array values consistently typed.

Using Application.Match instead of a loop

For a straightforward exact lookup, Application.Match can return the matching position. Passing 0 as its third argument requests an exact match. Assign the result to a Variant and test it with IsError; when there is no match, Application.Match returns an error value rather than a normal position.

Public Sub FindWithMatch()
    Dim fruits As Variant
    Dim matchPosition As Variant

    ' Array creates a one-dimensional list for this example.
    fruits = Array("Apple", "Pear", "Plum")
    matchPosition = Application.Match("Pear", fruits, 0)

    If IsError(matchPosition) Then
        MsgBox "No exact match was found."
    Else
        MsgBox "Found at array position " & CStr(matchPosition)
    End If
End Sub

The returned position is the item’s position in the lookup list, counted from 1, not necessarily its VBA array index. For the example, “Pear” is the second item, so Match returns 2 even though the Array function created indexes 0, 1, and 2. If you need the actual array index, or want custom comparison rules, a loop is often easier to reason about.

Match is not always a drop-in replacement for a loop. Exact text matching is not case-sensitive, and wildcard characters can have special meaning in text searches. Prefer an explicit loop with StrComp when you need case-sensitive matching or literal handling of characters such as an asterisk or question mark.

Checking a worksheet range

If the items are in a single row or column on a worksheet, you can pass the range directly to Match. This example assumes the values are in cells A2:A20 on the active sheet:

Public Sub FindInWorksheetColumn()
    Dim matchPosition As Variant

    ' Search A2:A20 on the active worksheet for an exact match.
    matchPosition = Application.Match("Pear", Range("A2:A20"), 0)

    If IsError(matchPosition) Then
        MsgBox "Pear is not in A2:A20."
    Else
        MsgBox "Pear is at position " & CStr(matchPosition) & _
               " within A2:A20."
    End If
End Sub

For production code, qualify the range with a worksheet variable rather than relying on the active sheet; otherwise, the result can change if a different sheet is active when the macro runs. Match returns a position within the searched range, not a worksheet row number. In this example, position 1 corresponds to A2, position 2 to A3, and so on. Keep the lookup range to one row or one column so its search direction is unambiguous.

Why a range’s Value2 array needs different handling

A frequent source of confusion is that a range and an array created with Array do not necessarily have the same shape. Reading multiple cells into a Variant with Range.Value2 produces a two-dimensional array: the first index represents rows and the second represents columns. A one-dimensional function that uses values(i) is not designed for that shape.

For a rectangular range, use two indexes. This example checks every cell in A2:C10 on the active worksheet, skips Excel error values, and looks for an exact value comparison:

Public Sub SearchTwoDimensionalRangeArray()
    Dim data As Variant
    Dim r As Long
    Dim c As Long
    Dim target As String
    Dim found As Boolean

    ' Value2 returns a two-dimensional array for this multi-cell range.
    data = Range("A2:C10").Value2
    target = "Pear"
    found = False

    For r = LBound(data, 1) To UBound(data, 1)
        For c = LBound(data, 2) To UBound(data, 2)
            If Not IsError(data(r, c)) Then
                If CStr(data(r, c)) = target Then
                    found = True
                    Exit For
                End If
            End If
        Next c
        If found Then Exit For
    Next r

    MsgBox "Found: " & CStr(found)
End Sub

This sample uses case-sensitive string comparison and converts non-error cell values to text. If the range may contain Null values or values that should be compared numerically, adjust the comparison instead of assuming all cells are ordinary text. A single-cell range can also behave differently from a multi-cell range when read into a Variant, so check the shape of the data before passing it to an array routine.

Choosing the right approach

Choose the method based on what the caller needs, not just which code looks shortest:

  • Use a loop when you need a Boolean answer, custom case handling, an actual array index, or control over how values are compared.
  • Use Application.Match for a concise exact lookup in a one-dimensional list or a one-row or one-column worksheet range, when its comparison behavior is suitable.
  • Use nested loops when the data is a two-dimensional Variant array from a rectangular range and you need to inspect every cell.
  • Use a Dictionary or Collection when you need repeated membership checks against a large set and the added setup is justified. These are lookup structures, not array methods.

A loop takes time proportional to the number of values inspected: if a match is near the beginning, it exits early; if it is absent, it checks every element. For a small list or a check done occasionally, clarity usually matters more than micro-optimizing. If you perform many searches against the same large list, building a lookup structure once may be more appropriate than rescanning the array for every target.

Common errors and practical safeguards

  • Assuming every array starts at zero: Use LBound and UBound. A fixed lower bound can skip values or cause a subscript error.
  • Passing a two-dimensional range array to a one-dimensional function: Use nested indexes for Value2 data, or search the worksheet range directly with Match when the range is one row or column.
  • Using WorksheetFunction.Match for a missing value: That form raises a run-time error when it cannot find a match. Application.Match instead returns an error Variant that can be tested with IsError.
  • Expecting case-sensitive Match behavior: Match does not distinguish upper- and lowercase text. Use a loop and StrComp with vbBinaryCompare if that distinction matters.
  • Comparing mixed or special values without checking them: Error values, Nulls, objects, and incompatible types can make an equality test fail. Define what should count as a match and guard those cases explicitly.
  • Assuming the workbook can undo a macro: Ctrl+Z does not reverse VBA actions. Save a backup before running code that changes workbook content.

Frequently Asked Questions

Does VBA have an array Contains function?
No general built-in Contains method exists for ordinary VBA arrays. Write a loop, or use Application.Match when its lookup behavior fits the data.

How do I check whether a VBA array contains a string without case sensitivity?
Loop through the elements and use StrComp(element, target, vbTextCompare) = 0. This makes the text comparison rule explicit. Skip or separately handle elements that cannot safely be converted to strings.

Can I use Match with an array created by Array?
Yes. For a one-dimensional list, use Application.Match(target, values, 0) for an exact lookup, then use IsError to detect a missing result. Remember that Match returns a position counted from 1.

Why does my array search fail on data from a worksheet range?
A multi-cell range read into a Variant with Value2 is two-dimensional, even when it has one column. Index it with row and column indexes, or search the one-column range itself with Match.

Can I search an array with a different lower bound?
Yes. Read its bounds with LBound and UBound rather than assuming the first index is 0 or 1. The loop function above uses those bounds for a one-dimensional array.

Bottom line

For most VBA code, a bounds-aware loop is the clearest way to answer whether a one-dimensional array contains a value. Use Match for a compact exact lookup when its case-insensitive behavior and returned position suit your needs, and handle worksheet range arrays as two-dimensional data rather than assuming they are ordinary one-dimensional lists.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.