Excel VBA INDEX: Return Values from a Range

In Excel VBA, “INDEX” usually means Excel’s INDEX worksheet function, called as Application.WorksheetFunction.Index(...) or Application.Index(...) to return an item from a range. It is not a standalone VBA keyword; indexing a VBA array is a separate operation that uses the array’s actual bounds.

What INDEX means in Excel VBA

Excel’s INDEX function locates a value—or, in some uses, a reference—at a position within a range or array. You give it a row number and, when needed, a column number. Those numbers describe positions inside the selected range, not worksheet row and column numbers. For example, row 3 and column 2 of A2:C10 means cell B4.

That distinction is a frequent source of mistakes. If the target is on worksheet row 4, its relative row number in a range starting at row 2 is 3. INDEX counts from the range’s top-left cell, which is position 1, rather than using the sheet’s row labels.

In a worksheet cell, with data in A2:C10, this formula returns the value in B4:

=INDEX(A2:C10,3,2)

VBA can call the same Excel function to retrieve a value for a macro, put the result in another cell, or use it as part of a larger procedure.

INDEX syntax and relative positions

The common array form is INDEX(array, row_num, [column_num]). The array is the range or array to search; row_num selects a row within it, and column_num selects a column. The column argument can be omitted for a one-column range. Excel also has a reference form that can work with multiple areas and an optional area number, but the array form covers most VBA lookups.

Suppose a worksheet named Data has product names in A2:A6 and prices in B2:B6. In the range B2:B6, position 4 is B5, regardless of the fact that B5 is worksheet row 5. If a macro has already determined the position of a product, INDEX can retrieve the corresponding price without constructing a cell address as text.

Use a row position of 1 for the first row of the supplied range.
Use a column position of 1 for the first column of the supplied range.
For a multi-column range, provide both positions when you need one specific cell.
Keep the selected position within the range’s dimensions; an invalid position can produce an error.

Calling INDEX from a VBA macro

The following example reads the value at the third row and second column of A2:C10, then writes it to E2 on the same sheet. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Option Explicit

Sub GetIndexValue()
    Dim source As Range
    Dim result As Variant
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    Set source = ws.Range("A2:C10")

    ' The third row and second column of A2:C10 is cell B4.
    result = Application.WorksheetFunction.Index(source, 3, 2)
    ws.Range("E2").Value = result
End Sub

This example expects a worksheet named Data and an existing range A2:C10. If the sheet name is different or missing, VBA will stop with an error before INDEX runs. The result is the value currently stored in B4, and E2 receives that value. The macro copies the value, not a live link: if B4 changes later, E2 does not automatically update.

To make the position depend on a variable, declare it and pass it in place of the fixed number. For instance, a rowPosition value of 3 means the third row within source, not worksheet row 3. This is useful when another part of a procedure has already found a matching row.

WorksheetFunction.Index or Application.Index?

Both expressions call Excel’s INDEX calculation, but their error behavior is worth knowing. Application.WorksheetFunction.Index is the explicit WorksheetFunction form. If the worksheet function would return an error, the call can raise a VBA runtime error, so a procedure may need error handling.

Application.Index is often useful when you want to inspect a result with VBA’s IsError function instead of immediately handling a runtime error. Depending on the input and arguments, it can return a value, an array, or an error value. That flexibility is helpful, but it also means the receiving variable should generally be a Variant when the result type is not guaranteed.

For a straightforward, valid range and position, either form can work. Choose based on how the macro should handle invalid input, and test the exact case your procedure uses. Do not assume that changing the call’s spelling fixes an out-of-range position; validate positions and ranges as part of the macro’s logic.

INDEX is not the same as VBA array indexing

In VBA, an array element is accessed with parentheses, such as items(2). That syntax does not call Excel’s INDEX function. The number inside the parentheses is an array subscript, and its valid range depends on how the array was created.

For example, an array loaded from a multi-cell range with Range.Value or Range.Value2 is typically a two-dimensional Variant array with row and column subscripts beginning at 1. An array declared with fixed bounds can have different lower bounds, and arrays created with Array(...) are commonly zero-based unless module settings or other construction choices change the bounds. Use LBound and UBound when the limits are not certain.

This matters when switching between a worksheet range and a VBA array. INDEX row 1 means the first row of the supplied range; array subscript 1 means the element whose actual subscript is 1. These often line up for a range-derived array, but they are not a universal rule for every VBA array.

Common errors and how to prevent them

A frequent problem is confusing absolute worksheet coordinates with relative INDEX positions. If the source is D5:F20, the first row is worksheet row 5 but INDEX row 1. Similarly, column 1 means D, not worksheet column A. Confirm the range’s top-left cell before calculating positions.

Another issue is supplying a position that is zero or larger than the relevant dimension. In the common array form, zero can have special behavior in Excel’s INDEX function—for example, it can return a whole row or column in supported contexts—rather than selecting a normal single cell. That can yield an array result where the macro expected one value. If you intend to return one cell, validate that the row and column positions are positive and within the range’s row and column counts.

Confirm that the worksheet and source range exist before calling INDEX.
Use source.Rows.Count and source.Columns.Count to check position limits.
Use a Variant for results that might be an error or an array.
Decide explicitly what the macro should do when a lookup position is not found.
Test with a small sample where the expected cell is easy to identify.

Also distinguish an empty cell from a failed lookup. INDEX returns the content at the selected position; if that cell is blank, the macro may receive an empty value. It does not independently check whether a match was found or whether the selected value is meaningful. The code that calculates the position should handle a missing match separately.

When INDEX is useful—and when another approach fits

INDEX is a good fit when you know a position or have calculated one and want the value at that position. It is also useful in worksheet formulas because the returned value can be combined with other functions. In VBA, it can be clearer than building a cell address from row and column numbers, especially when the source range might move but its dimensions and relative positions remain meaningful.

For a simple direct cell read, VBA can use a Range object instead. If you already know the address, reading Range("B4").Value is more direct than asking INDEX for row 3, column 2 of A2:C10. If you need to locate a value by its contents rather than by a known position, use a search method such as the Range Find method or a suitable worksheet lookup function, then use the result deliberately. INDEX itself does not search for a matching label.

For large data sets, loading a range into a Variant array and working with the data in memory can reduce repeated worksheet reads. That is a broader performance choice, not a reason to treat INDEX as an array-search function. Choose the method that makes the lookup logic and error cases easiest to verify.

Availability, macros, and safety

VBA runs in the desktop versions of Excel, including Microsoft 365 for Windows and Excel for Mac, though some VBA features and object-model details differ between platforms. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros. A macro that uses only basic worksheet ranges is more portable than one that depends on Windows-specific components, but it should still be checked on the target platform.

On Windows, the Visual Basic Editor can be opened with Alt+F11; on Mac, the shortcut is commonly Option+F11, with the Fn key sometimes needed depending on the keyboard and system settings. The Developer tab can be enabled from Excel Options on Windows or Excel Settings on Mac if it is not visible. Macro security settings may block downloaded or untrusted files. Enable macros only for workbooks whose source and code you trust, and keep a backup before running code that writes or changes data.

Frequently Asked Questions

Is INDEX a VBA function?
INDEX is an Excel worksheet function, not a built-in VBA language keyword. A macro can call it through Application.Index or Application.WorksheetFunction.Index.

How do I return a cell value with INDEX in VBA?
Set a Range variable for the source, then assign a call such as Application.WorksheetFunction.Index(source, 3, 2) to a Variant. The positions refer to the third row and second column inside that range.

Why does my INDEX result come from the wrong row?
The row number is relative to the source range, not the worksheet. For a range beginning in row 5, INDEX row 1 refers to worksheet row 5. Check both the range’s starting cell and the position you pass.

Can INDEX search for a name or ID?
No. INDEX returns an item from a position; it does not find the position by matching a name or ID. First determine the matching position with a lookup or search method, then use INDEX if that approach suits the task.

Can Excel for the web run an INDEX VBA macro?
No. Excel for the web does not run VBA macros. Use desktop Excel to run the macro, or consider a worksheet formula or Office Scripts for a browser-based workflow.

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.