VBA Array: Find a Value and Get Its Index in Excel

To find a value in a VBA array and get its index, loop from LBound to UBound and return the loop variable when the value matches. The loop variable is the array’s actual subscript, which may not be the same as its position when counting from one.

Quick answer: search a one-dimensional array

This function searches an initialized, one-dimensional array and returns True when it finds an exact match. It puts the matching array index in foundIndex. If there is no match, it returns False; the Boolean result, not the numeric output variable, tells you whether the search succeeded.

Option Explicit

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

    ' Set a predictable output when no match is found.
    foundIndex = 0

    ' An unallocated dynamic array has no usable bounds.
    On Error GoTo InvalidArray
    lowerBound = LBound(values)
    upperBound = UBound(values)
    On Error GoTo 0

    ' The loop variable is the actual array subscript.
    For i = lowerBound To upperBound
        If values(i) = sought Then
            foundIndex = i
            TryFindIndex = True
            Exit Function
        End If
    Next i

    Exit Function

InvalidArray:
    ' Return False if the array has no readable bounds.
    Err.Clear
    On Error GoTo 0
End Function

Public Sub DemoFindIndex()
    Dim values As Variant
    Dim foundIndex As Long

    ' VBA.Array gives this example a zero-based array.
    values = VBA.Array("Apple", "Orange", "Pear", "Plum")

    If TryFindIndex(values, "Pear", foundIndex) Then
        MsgBox "Found at index " & foundIndex
    Else
        MsgBox "Value not found"
    End If
End Sub

With the example values, the message reports index 2, because the first element is at index 0. The function is designed for an initialized one-dimensional array. For a multidimensional array, a range array, or a search that should ignore capitalization, adjust the approach as described below.

Index versus position: why the distinction matters

An index is the subscript VBA uses to address an element; a position is often how a person counts through a list. They match only when the array’s lower bound is 1. A five-element array declared as Dim items(1 To 5) As String has indices 1 through 5. An array created with VBA.Array has indices 0 through 3 in the example above. Both contain four values, but their index numbers differ.

Use LBound(array) to get the lowest valid index and UBound(array) to get the highest. These functions make a loop work with arrays whose bounds are not the ones you expected. Avoid assuming that the first index is always zero or one: declarations can specify other bounds, and the module’s Option Base setting affects some arrays created without an explicit lower bound.

If someone asks for the position counted from one rather than the VBA subscript, calculate it after finding the index: position = foundIndex - LBound(values) + 1. For an array with a lower bound of 0, index 2 is position 3. For an array with a lower bound of 1, index 3 is also position 3.

How the search loop works

The loop checks each element in turn, starting at the lower bound. When it finds a match, it stores i and exits immediately; continuing would waste time if the first match is all you need. If the loop reaches the upper bound without finding one, the function’s default Boolean return value remains False.

This is a linear search: in the worst case it checks every element. That makes it straightforward and suitable for many ordinary VBA tasks, especially when the array is not huge or the values are not sorted. If duplicates exist, this version returns the first matching index in the direction of the loop. To find every matching index, collect matches in another array or process each match instead of exiting on the first one.

The separate Boolean result avoids relying on a special index such as -1 to mean “not found.” Arrays can have unusual lower bounds, so a chosen number might itself be a valid index. When calling the function, use the Boolean in the If statement and read foundIndex only when it returns True.

Bounds, empty arrays, and array types

A fixed-size array has bounds as soon as it is declared. A dynamic array declared with empty parentheses, such as Dim values() As Long, does not have usable bounds until it has been allocated with ReDim or populated in another way. Calling LBound or UBound on an unallocated dynamic array raises an error. The function handles that case around the bounds lookup and reports no match.

That error handling is deliberately limited to obtaining the bounds. It does not make every possible array safe to search: the function expects a one-dimensional array whose elements can be compared with the requested value. A multidimensional array requires multiple loop variables and an element reference such as values(row, column). An array of objects also needs object-aware comparison, commonly using Is rather than =.

Use a suitable type for index variables. VBA’s Long is the usual choice for array indices; an Integer has a much smaller range and can overflow in larger data-processing tasks. For a typed array such as Dim numbers(1 To 10) As Long, the same loop logic applies, although the sample function accepts a Variant so it can receive an array stored in a Variant variable.

Exact text matches and case-insensitive searches

The comparison values(i) = sought is an equality check, not a partial-text search. With strings, the module’s Option Compare setting can affect whether comparisons are binary or textual; a module with no Option Compare Text statement uses binary comparison by default. For code that should state its case behavior explicitly, use StrComp.

To make a case-insensitive comparison for string elements, replace the condition inside the loop with If StrComp(CStr(values(i)), CStr(sought), vbTextCompare) = 0 Then. The CStr conversions make the intended string comparison explicit, but they are appropriate only when the elements and search term can be converted to strings. For numeric data, compare numbers as numbers; converting everything to text can produce unintended matches or formatting differences.

A partial match is a different requirement. For example, InStr can test whether one string occurs inside another, but it does not return an array index by itself. Put that test inside the same loop and record i when it succeeds. Decide whether an empty search string should count as a match before using this method, because it can produce surprising results.

Searching values read from an Excel range

A multi-cell range assigned to a Variant usually becomes a two-dimensional array, even when the range occupies a single row or column. Its elements are accessed with two subscripts, commonly row and column, so passing that array to the one-dimensional function and using values(i) is not the right pattern. For a range in one column, loop over the row bound and use the column bound too: conceptually, compare data(r, 1) and return r when it matches.

Keep the meaning of the returned number clear. If you load A2:A10 into an array, the array’s row subscript is not automatically the worksheet row number. The first array row corresponds to worksheet row 2, so translate between them using the range’s starting row. For example, if the first matching array row is 1, the corresponding worksheet row is 2. A search directly against worksheet cells can instead return a cell’s Row property, but that is a worksheet row number, not an array index.

For a single cell, Excel may provide a scalar rather than a two-dimensional array, so code intended to handle both one-cell and multi-cell ranges needs to account for that difference. If the data is already on a worksheet and you want a built-in search rather than a VBA loop, the Excel object model’s Range.Find method is another option. Its search settings can persist between calls and interact with Excel’s Find dialog, so specify relevant arguments instead of relying on remembered settings.

Common mistakes and alternatives

Starting at zero or one by assumption: use LBound and UBound rather than hard-coded limits.
Confusing the index with a worksheet row: convert explicitly from the array’s position to the range’s starting row.
Using a missing-value number as the only signal: a Boolean result avoids confusing a valid subscript with “not found.”
Searching a two-dimensional range array as if it were one-dimensional: include both subscripts in the comparison.
Expecting a partial match from equality: use a string-search condition inside the loop when partial matches are intended.

For a short or unsorted array, a loop is transparent and easy to adapt. If the array is sorted and searched repeatedly, a binary search can reduce the number of comparisons, but it requires correctly sorted data and more careful boundary logic. For worksheet data, Range.Find may be more convenient; for a value that must be returned to a worksheet formula, Excel lookup functions may be a better fit than VBA. Choose based on whether you need an array subscript, a worksheet address, or just the matching value.

Running the VBA code safely

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the function and demonstration procedure into that module. Run DemoFindIndex from the editor or from Excel’s Macro dialog. Save the workbook as a macro-enabled workbook, such as an .xlsm file, if you need to retain the VBA code.

On a Mac, the Visual Basic Editor is available through the Developer tab or Tools > Macro > Visual Basic Editor, depending on the ribbon setup and Excel version; keyboard shortcuts differ from Windows and can vary with macOS settings. The VBA logic is the same, but use the Mac interface to open the editor and run the macro. Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the practical way to preserve the original if a procedure changes data unexpectedly.

Frequently Asked Questions

How do I get the position of a value in a VBA array?
Find its index with a loop, then calculate index - LBound(array) + 1 for a one-based position. This conversion is useful when you mean “first, second, third” rather than the actual VBA subscript.

What does VBA return if the value is not found?
A loop does not automatically return a special “not found” index. Use a Boolean result, as in TryFindIndex, or define a sentinel that cannot conflict with a valid index. Check the result before using the output index.

Can I use this code with a two-dimensional array?
Not unchanged. Add a loop for each dimension and compare an element using both subscripts, such as values(row, column). Decide whether the result should be the row, the column, or both.

Does the search return the first or last duplicate?
The sample returns the first match encountered, which is the lowest-index match when the loop runs upward from LBound. To get the last match, keep updating the saved index and do not exit on the first match.

Is VBA’s index the same as Excel’s row number?
No. An array index identifies an element within the array. A worksheet row number identifies a row on the sheet; translate between them using the range’s starting row or return the matched cell’s row directly.

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.