Excel VBA Find a Value in an Array: Loop, Match, and Tips

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, the function below returns True and the element’s actual index when it finds a match; otherwise, it returns False.

Search a one-dimensional array with a loop

A loop is the most direct approach because it works with ordinary VBA arrays and makes the search rules visible. Put this code in a standard module in the Visual Basic Editor. It searches a one-dimensional array, skips error and Null values, and compares two strings using binary comparison, which is case-sensitive.

Option Explicit

Public Function FindInVector(ByRef values As Variant, _
                             ByVal sought As Variant, _
                             ByRef foundIndex As Long) As Boolean
    Dim i As Long
    Dim lowerBound As Long
    Dim upperBound As Long

    foundIndex = 0
    If Not IsArray(values) Then Exit Function
    If IsError(sought) Then Exit Function
    If IsNull(sought) Then Exit Function

    On Error GoTo NoBounds
    lowerBound = LBound(values, 1)
    upperBound = UBound(values, 1)
    On Error GoTo 0

    For i = lowerBound To upperBound
        If Not IsError(values(i)) Then
            If Not IsNull(values(i)) Then
                If VarType(values(i)) = vbString And _
                   VarType(sought) = vbString Then
                    If StrComp(CStr(values(i)), CStr(sought), _
                               vbBinaryCompare) = 0 Then
                        foundIndex = i
                        FindInVector = True
                        Exit Function
                    End If
                ElseIf values(i) = sought Then
                    foundIndex = i
                    FindInVector = True
                    Exit Function
                End If
            End If
        End If
    Next i
    Exit Function

NoBounds:
    FindInVector = False
End Function

Public Sub DemoFindInVector()
    Dim items As Variant
    Dim position As Long
    Dim wasFound As Boolean

    items = Array("Red", "Green", "Blue")
    wasFound = FindInVector(items, "Green", position)

    If wasFound Then Debug.Print "Found at index " & position
End Sub

In this example, the array made by Array has indexes 0, 1, and 2, so the Immediate window prints Found at index 1. The function returns the array’s real subscript, not a count of how many elements it has passed. The Boolean result tells you whether the index is meaningful. That distinction matters because 0 is a valid index in many VBA arrays.

The function accepts a Variant containing an array, which accommodates arrays created with Array and many arrays read from worksheet ranges. It returns False if the argument is not an array or has no usable bounds. It is intended for a one-dimensional array of scalar values; a two-dimensional range array needs a nested loop, shown below.

Why LBound and UBound matter

Do not assume that every VBA array starts at zero or that its final index equals its number of elements minus one. Array("A", "B") normally creates a zero-based array, while an explicitly declared array can use different bounds. For example, a declaration may start at 1, and a dynamic array can be resized with ReDim to bounds chosen by the code. LBound and UBound report the actual lower and upper bounds.

A frequent mistake is to write For i = 0 To UBound(values). That may skip elements in a one-based array, or attempt an invalid subscript when the array starts somewhere else. The opposite assumption—starting every search at 1—fails on arrays created by Array. Iterating from the reported lower bound through the upper bound avoids both errors.

Bounds are not the same as a count. For an array whose indexes run from 4 through 8, the number of elements is five, but its indexes are not 1 through 5. Use the returned index directly when accessing the array. If you need a position numbered from one within the array, calculate it as i - lowerBound + 1; keep that relative position distinct from the actual subscript.

Search values read from a worksheet range

In Excel for Microsoft 365 on Windows, assigning a multi-cell range’s Value2 to a Variant produces a two-dimensional array. Its first dimension represents worksheet rows and its second dimension represents columns. For example, a two-column range can be searched with an outer loop over rows and an inner loop over columns. The function below returns the worksheet-array subscripts where it first finds the value.

Public Function FindInMatrix(ByRef values As Variant, _
                             ByVal sought As Variant, _
                             ByRef foundRow As Long, _
                             ByRef foundColumn As Long) As Boolean
    Dim r As Long
    Dim c As Long

    If Not IsArray(values) Then Exit Function
    If IsError(sought) Then Exit Function
    If IsNull(sought) Then Exit Function

    For r = LBound(values, 1) To UBound(values, 1)
        For c = LBound(values, 2) To UBound(values, 2)
            If Not IsError(values(r, c)) Then
                If Not IsNull(values(r, c)) Then
                    If values(r, c) = sought Then
                        foundRow = r
                        foundColumn = c
                        FindInMatrix = True
                        Exit Function
                    End If
                End If
            End If
        Next c
    Next r
End Function

Public Sub DemoFindInRange()
    Dim data As Variant
    Dim rowIndex As Long
    Dim columnIndex As Long
    Dim wasFound As Boolean

    data = ThisWorkbook.Worksheets("Sheet1").Range("A2:B20").Value2
    wasFound = FindInMatrix(data, "Green", rowIndex, columnIndex)

    If wasFound Then Debug.Print rowIndex, columnIndex
End Sub

For the example range, the array’s first row corresponds to worksheet row 2 and its first column corresponds to worksheet column A. Thus, returned subscripts are relative to the array, not worksheet coordinates. Convert them when needed: worksheet row is rowIndex + 1 for this range, and worksheet column is columnIndex. More generally, add the range’s starting row or column minus one.

A single-cell range is an important exception: assigning its Value2 to a Variant gives a scalar value rather than a two-dimensional array. Handle that case separately, or read a range containing multiple cells when the code requires array bounds. On Mac, the VBA Editor and the underlying array behavior are similar, though menu names and shortcuts can differ from Windows.

Choose the right comparison rule

Text matching is often more complicated than checking whether two values look alike. The first function uses StrComp with vbBinaryCompare when both values are strings, so Green and green are different. To make that string comparison case-insensitive, change vbBinaryCompare to vbTextCompare. This makes the comparison ignore letter case according to VBA’s text comparison rules.

Be precise about what “equal” means in your data. A number stored as text, such as the string "12", may not behave like the numeric value 12 in every comparison or downstream operation. Leading and trailing spaces also remain part of a string; "Green " is not the same string as "Green". If spaces should not count, normalize both sides deliberately, for example by applying Trim$ to string values before comparing them.

The sample skips cells containing Excel error values because comparing an error value with an ordinary target can fail. It also skips Null values. Empty cells read from a range commonly arrive as Empty Variants, not Null; decide whether an empty target should match those cells before adapting the comparison. Avoid converting every value with CStr just to simplify matching: that can collapse distinctions between numbers, dates, and text or produce unwanted conversions.

Use Match when you need a position

For a one-dimensional lookup, Excel’s Match worksheet function can be a compact alternative. In VBA, call it through Application.Match and pass 0 as the third argument to request an exact match. Its result is a position in the lookup vector, not necessarily the VBA array’s actual subscript. It returns an error value when it cannot find the target, so store the result in a Variant and test it with IsError.

Public Sub DemoApplicationMatch()
    Dim items As Variant
    Dim result As Variant

    items = Array("Red", "Green", "Blue")
    result = Application.Match("Green", items, 0)

    If IsError(result) Then
        Debug.Print "Not found"
    Else
        Debug.Print "Position in vector: " & result
    End If
End Sub

Here, the result is 2 because Green is the second item in the lookup vector, even though its subscript in the zero-based array is 1. That difference is a common source of off-by-one errors. A loop is usually clearer when you need the actual array index, custom comparison rules, every matching item, or a search across two dimensions. Application.Match suits a straightforward one-dimensional lookup where a relative position is sufficient.

Find the first match or every match

The examples stop at the first match by using Exit Function or completing the search after finding a result. This is appropriate when values are unique or when the first occurrence is the one you want. If duplicates are possible, define which occurrence matters: first, last, or all. A first-to-last loop returns the first occurrence; reversing the loop returns the last, provided you use the array’s actual bounds.

To collect every match, do not exit after finding one. Instead, increment a match counter and store each matching index in a separate array or collection. If the number of matches is not known in advance, a Collection can be convenient; a dynamic array is another option but requires resizing. Be aware that repeatedly using ReDim Preserve inside a large loop can add overhead. For a large number of searches, a dictionary keyed by the values may be more suitable, especially when you need fast repeated membership checks and can define how duplicate keys are handled.

For a worksheet lookup that only needs to return a related value, a worksheet formula such as XLOOKUP may be simpler than writing a macro. In Excel for Microsoft 365 and Excel 2021, XLOOKUP is available; it is not available in every older Excel release. VBA array searching is more useful when the data is already in memory, the search is part of a larger macro, or the required rule is not conveniently expressed in a worksheet formula.

Common errors and practical safeguards

Subscript out of range: Check that the array is initialized and that the loop uses the correct dimension and bounds. An uninitialized dynamic array may not have usable bounds.
Type mismatch: Check for error values and Null before comparing. If your array can contain objects or nested arrays, handle those types separately instead of treating every element as a scalar.
Wrong result position: Decide whether you want an actual VBA subscript, a one-based position in a vector, or a worksheet row and column. They are different coordinate systems.
Unexpected text match: Choose binary or text comparison deliberately, and account for spaces, punctuation, and text-versus-number values.
Search finds only one duplicate: Remove the early exit and save each matching location if the task requires every occurrence.

For efficient searches in a worksheet, read a multi-cell range into a Variant array once, search that array, and write results back in batches if necessary. Repeatedly reading individual worksheet cells inside a long loop can be slower because each cell access crosses between VBA and Excel’s worksheet object model. For a short range or a one-off lookup, however, a simple cell loop may be easier to understand than building extra data structures.

Test the search with a value that exists, one that does not, a value at each end of the array, and any special cases your data allows, such as blanks or error cells. VBA macros cannot be undone with Ctrl+Z, so save a copy of the workbook before running a macro that changes worksheet data. The search functions above only read values, but a larger macro that uses their results may make changes that cannot be reversed that way.

Frequently Asked Questions

How do I check whether a value exists in a VBA array?
Use a Boolean-returning function that loops from LBound to UBound and returns True when it finds a match. The first example does this and also returns the matching subscript.

Does VBA have a built-in array Find function?
There is no general-purpose VBA Array.Find function for ordinary arrays. A loop is the usual flexible method; Application.Match is an alternative for a one-dimensional exact lookup when a relative position works for your task.

How do I make a VBA array search case-insensitive?
When comparing two strings with StrComp, use vbTextCompare instead of vbBinaryCompare. Also decide whether leading or trailing spaces should be removed, since ignoring case does not remove spaces.

Why does Match return 2 when the VBA array index is 1?
Application.Match returns the item’s position in the lookup vector, counted from 1. A zero-based VBA array can have that same item at subscript 1, so the two results use different numbering.

How do I search a range stored in a Variant array?
A multi-cell range read with Range.Value2 is a two-dimensional array. Loop through dimension 1 for rows and dimension 2 for columns, using LBound and UBound for each dimension. Handle a single-cell range separately because it returns a scalar.

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.