VBA Find in Array: Choose the Right Search Method

To find a value in a VBA array, loop from LBound to UBound and compare each element with the value you want. For a one-dimensional array of strings, a small function using StrComp makes the case-sensitivity rule explicit; for a worksheet range or a simple exact lookup, Application.Match can be shorter.

What “find in array” means in VBA

VBA does not provide a general-purpose built-in Find method for every array. The usual approach is to examine the elements and decide what counts as a match. That gives you control over whether text must match case, whether spaces matter, what to return when there is no match, and how to search arrays with more than one dimension.

First decide what the result should be. A search can return the matching value, its array index, a Boolean indicating whether it exists, or the row and column where it was found. Returning an index is often useful, but remember that an array’s first index is not necessarily zero or one. Use its actual lower bound rather than assuming a particular starting point.

The examples below are VBA for Excel on Microsoft 365 for Windows. The core array operations also apply in other VBA hosts, but Excel-specific objects such as Application.Match are not general VBA features.

A reusable case-insensitive search for strings

This function searches a one-dimensional, initialized array and reports the matching element’s actual index through foundIndex. It returns True when it finds a match and False otherwise. The comparison is case-insensitive because it uses vbTextCompare.

Function FindStringIndex(ByRef values As Variant, _
                         ByVal target As String, _
                         ByRef foundIndex As Long) As Boolean
    Dim i As Long

    ' Search the array's actual index range.
    For i = LBound(values) To UBound(values)
        ' Ignore worksheet errors and Null values.
        If Not IsError(values(i)) Then
            If Not IsNull(values(i)) Then
                If StrComp(CStr(values(i)), target, vbTextCompare) = 0 Then
                    foundIndex = i
                    FindStringIndex = True
                    Exit Function
                End If
            End If
        End If
    Next i
End Function

For example, with a zero-based string array containing "Red", "Green", and "Blue", searching for "green" returns True and sets foundIndex to 1. If no item matches, the function returns False; the caller should use the index only after checking that Boolean result.

The function expects a one-dimensional array that has been initialized. It is intended for scalar values that can be converted to strings; it skips array elements that are Excel error values or Null. It does not handle an unallocated dynamic array, a multidimensional array, or nested arrays. Check those cases separately if your data can contain them.

How to call the search function

Put the function in a standard VBA module, then call it from another procedure. This example creates a small array and displays either the found index or a not-found message.

Sub ExampleStringSearch()
    Dim items As Variant
    Dim foundAt As Long
    Dim wasFound As Boolean

    ' Array uses the bounds supplied by the Array function and Option Base.
    items = Array("Red", "Green", "Blue")
    wasFound = FindStringIndex(items, "green", foundAt)

    If wasFound Then
        MsgBox "Found at array index " & foundAt
    Else
        MsgBox "Value not found"
    End If
End Sub

The reported number is a VBA array index, not necessarily a worksheet row number. If the values came from cells beginning in A2, for example, a returned position within the loaded array must be mapped back to the corresponding worksheet row. Also, Array creates a Variant array whose lower bound can be affected by the module’s Option Base setting. Using LBound and the returned index avoids baking an assumption into the search.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even when a search routine is part of a larger procedure that changes cells.

Choosing case-sensitive or exact comparisons

“Exact” can mean different things. A case-insensitive text match treats "North" and "north" as equal. A case-sensitive match treats them as different. A match that ignores leading or trailing spaces is a different rule again; the search code should not silently remove spaces unless that is what the data rules require.

Case-insensitive strings: use StrComp(a, b, vbTextCompare).
Case-sensitive strings: use StrComp(a, b, vbBinaryCompare).
Default string comparison: the = operator follows the module’s Option Compare setting. If that setting is omitted, VBA uses binary comparison.
Ignore surrounding spaces: compare Trim$(CStr(value)) with the trimmed target, but only if spaces are insignificant in your data.

For numeric arrays, compare numeric values rather than converting everything to text. String conversion can create misleading matches—for example, numeric formatting and text representations may differ even when the underlying numbers are equal. For floating-point calculations, exact equality can also be unsuitable when the values result from arithmetic; choose an appropriate tolerance based on the calculation instead of treating every tiny rounding difference as a meaningful distinction.

Searching numeric and mixed-type arrays

For a numeric array, the central loop can use a direct comparison: If values(i) = target Then. Declare the target and index with appropriate types, and ensure the elements are comparable. A direct comparison is concise, but a Variant array can contain values of different types, including errors, objects, or Null. Those values can cause a comparison to fail or behave differently from ordinary numbers and strings.

If the array is guaranteed to contain only numbers, a clear search function can follow this pattern:

Function FindLongIndex(ByRef values As Variant, _
                       ByVal target As Long, _
                       ByRef foundIndex As Long) As Boolean
    Dim i As Long

    ' This version assumes a nonempty, one-dimensional numeric array.
    For i = LBound(values) To UBound(values)
        If values(i) = target Then
            foundIndex = i
            FindLongIndex = True
            Exit Function
        End If
    Next i
End Function

This function is deliberately narrower than the string example: its caller must supply a suitable initialized array of numeric values. If the array may contain Empty, text, Null, or error values, define how each should be treated and test for those cases before comparing. Narrow assumptions make a routine easier to understand and safer to reuse than a supposedly universal function with unclear conversion rules.

Searching a two-dimensional array

A two-dimensional array needs two loops, one for each dimension. Do not use one LBound or UBound call as if it described every dimension: pass the dimension number, such as LBound(data, 1) and UBound(data, 2). The following example searches string-like values and stops at the first match in row-major loop order.

Sub FindInTwoDimensions()
    Dim data As Variant
    Dim target As String
    Dim r As Long
    Dim c As Long
    Dim found As Boolean
    Dim foundRow As Long
    Dim foundColumn As Long

    ' Example assumes data has already been assigned a 2D array.
    target = "North"

    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 Not IsNull(data(r, c)) Then
                    If StrComp(CStr(data(r, c)), target, vbTextCompare) = 0 Then
                        found = True
                        foundRow = r
                        foundColumn = c
                        Exit For
                    End If
                End If
            End If
        Next c

        If found Then Exit For
    Next r
End Sub

In real code, assign the array before the loop and decide what to do with foundRow and foundColumn afterward. Those are array indices, not automatically Excel worksheet coordinates. When an array is loaded from a range, its bounds and the range’s starting cell both matter when translating a location back to the sheet. If every match is needed, remove the early exits and collect each pair of indices instead.

Using Application.Match instead of a loop

In Excel VBA, Application.Match can be a compact option for finding an item in a one-dimensional lookup array or a single-row or single-column range. Pass 0 as the third argument to request an exact match. The result is a one-based position within the lookup array, not necessarily the array’s VBA index.

Sub ExampleMatch()
    Dim lookupValues As Variant
    Dim result As Variant

    ' Assumes a one-dimensional array and an exact text lookup.
    lookupValues = Array("Red", "Green", "Blue")
    result = Application.Match("Green", lookupValues, 0)

    If IsError(result) Then
        MsgBox "Value not found"
    Else
        MsgBox "Match position: " & result
    End If
End Sub

For this example, the result is 2, because Match positions start at one. If an item is not found, Application.Match returns an error value, so test it with IsError before using the result as a number. This is different from calling WorksheetFunction.Match, which raises a VBA run-time error when no match exists unless the error is handled.

Use a loop when you need the actual VBA index, custom comparison logic, a multidimensional search, or control over unusual values. Use Match when its lookup behavior fits and a relative position is enough. Match does not automatically make a case-sensitive text search; use an explicit VBA comparison when case sensitivity matters.

Common problems and how to prevent them

Assuming the array starts at zero: arrays can have different lower bounds. Use LBound and UBound, including the dimension number for multidimensional arrays.
Using an uninitialized dynamic array: calling LBound or UBound on an unallocated dynamic array raises an error. Initialize it or handle the unallocated case before searching.
Confusing “not found” with index zero: zero can be a valid index. Return a Boolean separately or choose a sentinel that cannot be confused with a valid result.
Expecting a range array to be one-dimensional: a multi-cell Excel range read through Range.Value or Range.Value2 is generally represented as a two-dimensional Variant array, even when the range is one column wide. A single-cell range returns a scalar rather than a two-dimensional array.
Ignoring errors and Null: comparing an Excel error value or Null as if it were ordinary text or a number can cause problems. Test for these values if they are possible in the source.
Searching only for the first occurrence by accident: an early Exit Function or Exit For stops at the first match. Remove it or continue the loop if you need every occurrence.

For a range containing many cells, loading its values into a Variant array and searching in memory is often more efficient than reading each cell from the worksheet inside the loop. The conversion and search still have a cost, so the best approach depends on how much data you have and whether the values are already in an array.

Frequently Asked Questions

How do I check whether a value exists in a VBA array?
Loop through the bounds and set a Boolean to True when an element matches. A reusable function can return that Boolean and provide the found index separately, which avoids relying on a special index value to mean “not found.”

How do I find a string without regard to capitalization?
Compare strings with StrComp(value, target, vbTextCompare) and check whether the result is zero. Use vbBinaryCompare instead when uppercase and lowercase letters must be treated as different.

Does VBA have an array Find function?
There is no general VBA Find method that searches every kind of array. A loop is the flexible standard approach; Excel VBA’s Application.Match is an alternative for compatible one-dimensional lookups.

Why does LBound give me an error?
A common cause is calling it on a dynamic array that has not been allocated. Another is treating a scalar value—such as the result of reading one worksheet cell—as an array. Confirm that the variable contains an initialized array before asking for its bounds.

Can I use Match to find a value in a two-dimensional array?
Match is designed for a lookup array arranged as one row or one column, rather than a general search across a two-dimensional block. Use nested loops when the target could be anywhere in a two-dimensional array, or search an appropriate single row or column.

Which method should you use?

Use a loop when you want predictable index handling, case-sensitive comparisons, or a search across multiple dimensions. Choose Application.Match when you are working in Excel, the lookup data is arranged in one dimension, and its one-based relative position is useful. In either case, make the comparison rule and not-found behavior explicit, and check array bounds rather than assuming them. Those small choices prevent the most common VBA search bugs.

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.