VBA Array Search: Find Values with Loops or Match

To search a VBA array, loop from LBound to UBound, compare each element with the value you want, and stop when you find a match. This approach works for arrays with different starting indexes and gives you control over exact, case-sensitive, and more specialized comparisons.

What an array search returns

An array search checks the elements stored in an array and reports whether a target value occurs. A useful search usually needs to answer two questions: was the value found, and at what index? The index is the element’s position in the array, not necessarily its ordinal position starting at one.

For example, VBA’s Array function normally creates a zero-based array. In Array("apple", "pear", "plum"), the value pear is at index 1. An array declared with an explicit lower bound, or affected by Option Base, may start at a different index. That is why a search should normally get its bounds from the array instead of assuming the first element is at index 0 or 1.

A loop is the most flexible general-purpose method. It searches in order and returns the first matching index. If a value appears more than once, you can either stop at that first match or keep looping to collect every matching index.

A reusable loop for a one-dimensional array

This function takes an initialized, one-dimensional Variant array and a target value. It returns the matching index and sets a separate Boolean flag to indicate whether it found the target. Using a flag avoids treating a particular number as a not-found result, which matters because an array can have a negative lower bound.

Public Function FindArrayIndex(ByRef items As Variant, ByVal wanted As Variant, ByRef found As Boolean) As Long
    Dim i As Long

    found = False
    For i = LBound(items) To UBound(items)
        If items(i) = wanted Then
            FindArrayIndex = i
            found = True
            Exit Function
        End If
    Next i
End Function

Put the function in a standard VBA module, such as one created with Insert > Module in the Visual Basic Editor. For example, a caller can put Array("apple", "pear", "plum") in a Variant variable and search for pear. The function returns index 1 and sets found to True. If the value is absent, found remains False; the returned numeric value should then be ignored.

The function deliberately assumes that the array has been initialized and that its elements can be compared with the target. Calling LBound or UBound on an uninitialized dynamic array raises an error, and comparing incompatible element types can also raise an error. Validate or initialize the input before calling this compact function if your data can be empty or inconsistent.

Before running any macro in a workbook, save a separate copy: changes made by VBA cannot be undone with Ctrl+Z. This function only returns a value, but the precaution is important for macros that also edit cells or workbook data.

Why LBound and UBound matter

LBound(array) returns the smallest valid subscript, while UBound(array) returns the largest. Using both makes a loop work with arrays whose indexes start at 0, 1, or another explicitly chosen number. A loop that starts at 1 can skip the first item in a zero-based array; a loop that starts at 0 can request an invalid element in an array that starts at 1.

Option Base 1 can change the default lower bound for arrays declared in a module, but it does not make it safe to hard-code a starting index. The Array function has its own default behavior, and arrays can also be created with explicit bounds. Reading the bounds from the actual array is clearer and less dependent on where the array came from.

For a two-dimensional array, one pair of bounds is not enough. Use LBound(data, 1) and UBound(data, 1) for the first dimension, and dimension 2 for the second. A two-dimensional search needs nested loops, with a decision about whether to return the row, the column, or both. Do not use the one-dimensional function above for a two-dimensional array.

Exact text, case sensitivity, and partial matches

The comparison operator in the basic example performs an equality comparison. For text, VBA’s default behavior can depend on the module’s Option Compare setting: Option Compare Binary compares text using binary ordering, while Option Compare Text uses a case-insensitive textual comparison. If the required behavior matters, make it explicit rather than relying on a module setting.

For a case-sensitive text search, use StrComp with vbBinaryCompare. A return value of zero means the strings match under that comparison. For a case-insensitive comparison, specify vbTextCompare. For example, with a one-dimensional array of strings, replace the If items(i) = wanted Then condition with If StrComp(CStr(items(i)), CStr(wanted), vbTextCompare) = 0 Then. The conversions are appropriate only when the elements are intended to be treated as text; do not convert mixed data blindly.

An exact comparison is different from a substring search. If the target is pear, an exact search does not find green pear. To find text contained within each string, use VBA’s InStr function inside the loop and test whether its result is greater than zero. Choose the comparison mode deliberately: substring matches can return more results than a whole-element equality check.

Searching for every occurrence

The example exits as soon as it finds a match, so it returns only the first occurrence. To find all occurrences, remove Exit Function and add each matching index to a collection or another result array. This is useful when duplicates are meaningful, such as locating every row number for a repeated product code.

For a small, fixed array, you can also count matches first, allocate a result array of the required size, and make a second pass to store the indexes. A collection is convenient when the number of matches is not known in advance, while a result array can be more convenient for later processing. Whichever method you use, preserve the original indexes if callers need to know where each match came from.

Be precise about whether the search should return the first match, the last match, or all matches. A loop that continues and overwrites a saved index returns the last matching position. A loop that exits immediately returns the first. Neither behavior is inherently better; it depends on what the calling code needs.

Using Application.Match instead

For a simple exact lookup in a one-dimensional list, Excel’s Application.Match can be a concise alternative. In VBA it returns a Variant containing a relative position, or an error value if no match is found. The third argument should be 0 for an exact-match search. Check the return with IsError before treating it as a number.

There are important differences from a loop. Match returns a position relative to the lookup array, not necessarily the array’s VBA subscript. It does not provide the same control over case-sensitive comparison as StrComp, and text searches in exact mode can interpret wildcard characters such as an asterisk or question mark as wildcards. It is therefore a poor choice when you require literal, case-sensitive text equality or need full control over type handling.

Application.Match is useful when its worksheet lookup behavior is exactly what you need and the data is a straightforward one-dimensional list. A VBA loop is usually easier to adapt for custom comparisons, unusual bounds, multiple dimensions, or collecting every match. Do not confuse a position returned by Match with a worksheet row number; add the appropriate offset if you are translating a relative position into a row on the sheet.

Searching values read from a worksheet

A common pattern is to load a range into a Variant array and search it in memory. For a multi-cell range, the range’s Value property normally produces a two-dimensional, one-based Variant array, even when the range occupies only one column. The first dimension represents rows and the second represents columns. In that case, use a loop over the row bounds and read the relevant column, such as data(rowIndex, 1).

One cell is a special case: reading the value of a single-cell range returns a scalar rather than a two-dimensional array. Code that assumes the result is always an array can fail when the selected range contains only one cell. If the range size can vary, handle the single-cell case separately or normalize the data into a consistent array structure before searching it.

Searching an array already in memory avoids repeatedly asking the worksheet for cell values inside a loop. This can matter when a macro performs many comparisons against a large range. For a single lookup, direct worksheet functions may be simpler; for repeated lookups, loading data once and searching in memory can reduce worksheet interaction. The best choice depends on the size of the data and how many searches you need to perform.

Common errors and practical fixes

Wrong starting index: Replace a hard-coded 0 or 1 with LBound and UBound.
Uninitialized dynamic array: Check that the array has been allocated before calling its bounds functions. An empty array may need a separate no-data path.
Wrong dimensions: Use the dimension argument for bounds on a two-dimensional array and index each dimension in the right order.
Unexpected text result: Decide whether the search is case-sensitive, exact, or a substring search, and use a comparison suited to that rule.
Only one duplicate is returned: The early exit is intentional. Remove it or collect matches if every occurrence is needed.
Match reports a surprising position: Match returns a relative position in its lookup array, not the original VBA subscript or a worksheet row number.

Also consider data types. A number stored as text may not behave like a numeric value in every comparison, and Empty, Null, or error values can require special handling. If the source is user-entered spreadsheet data, define how those cases should be treated rather than silently coercing every value.

Choosing a search method

Use a direct loop when you want transparent behavior and precise control. It is a strong default for arrays because it works with arbitrary bounds and can be adapted for case rules, substring tests, two-dimensional data, or all matches. Use Application.Match when you want a concise exact lookup and its relative-position and wildcard behavior fit the task.

For repeated lookups in a very large list, a linear scan checks elements one by one and can become costly when repeated many times. If the data supports it, a sorted array with a suitable search strategy or a keyed structure such as a Scripting.Dictionary can be more appropriate. A dictionary is designed for key-based lookup, but it is not a drop-in replacement for ordered array searching: it has different setup, key, and ordering considerations. Choose based on whether you need positions, duplicates, ordering, and custom comparisons.

For small arrays, clarity is usually more valuable than optimizing prematurely. First confirm that the search returns the intended result for duplicates, missing values, different bounds, and the text rules your application requires. Optimize only when repeated searches are a demonstrated bottleneck.

Frequently Asked Questions

How do I check whether a value exists in a VBA array?
Loop from LBound to UBound and set a Boolean flag to True when an element matches. Stop early if you only need to know whether at least one match exists.

Can VBA search an array without a loop?
For a simple one-dimensional exact lookup, you can use Application.Match with match type 0 and check its result with IsError. A loop is more suitable when you need case-sensitive comparison, custom logic, or every matching index.

Does VBA array search start at zero or one?
It depends on how the array was created and declared. Use LBound to find its actual lower index rather than assuming either zero or one.

How do I search a two-dimensional array?
Use nested loops and get the bounds for each dimension separately, for example with dimension arguments 1 and 2. Decide whether the result should identify a row, a column, or both.

How can I make a VBA search case-insensitive?
For text comparisons, use StrComp with vbTextCompare and treat a zero result as a match. Use vbBinaryCompare when letter case should matter.

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.