Index match on 2D array inside VBA

Question

This is not the usual case where the array is defined within Excel. It is defined within VBA, and I need to find the proper value given a value found in a cell in Excel. In cell A1 I have the value DE000C5RQDA9. In B1 I want to return RXM1.

The actual array is more of a (0 to 9, 0 to XX) so do not think a dictionary is feasible. XX changes dynamically.

Example

 Sub Test()

 Dim arr(0 To 1, 0 To 1)
 arr(0,0) = "RXM1"
 arr(0,1) = "UBM1"
 arr(1,0) = "DE000C5RQDA9"
 arr(1,1) = "DE000C5RQDD3"
 
With Application
   ActiveSheet.Cells(1, 2) = .Index(arr(0), .Match(ActiveSheet.Cells(1,1), arr(1), 0))
End with

 End sub

Firstly, arr(0) and arr(1) have no meaning for a 2D array. Then, you tried a code for an array with two rows and two columns. What the code should return for an array having 9 rows and 10 columns, supposing that the vale in Cells(1, 2) matches the one in column 4, row 3? — FaneDuru
– FaneDuru, Commented Apr 13, 2021 at 11:05
The idea is you can match a value in a 1D array. The good news is you can slice rows or columns in a 2D array, making them 1D arrays and (if want only using Index - Match) try matching (only) between the two such columns. This means that you must know what columns to match for the case of more than two columns in the array... — FaneDuru
– FaneDuru, Commented Apr 13, 2021 at 11:44

norie · Accepted Answer · 2021-04-13 10:47:49Z

3

You can use Application.Index to get the columns you want to use in the INDEX/MATCH.

The same idea could be used for rows.

Sub Test()
Dim arr(0 To 1, 0 To 1)
Dim col1 As Variant
Dim col2 As Variant

    arr(0, 0) = "RXM1"
    arr(0, 1) = "UBM1"
    arr(1, 0) = "DE000C5RQDA9"
    arr(1, 1) = "DE000C5RQDD3"

    col1 = Application.Index(arr, 1)
    col2 = Application.Index(arr, 2)
    With Application
        ActiveSheet.Cells(1, 2) = .Index(col1, .Match(ActiveSheet.Cells(1, 1), col2))
    End With

End Sub

answered Apr 13, 2021 at 10:47

norie

9,9372 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

T.M. Over a year ago

... or ... = .Index(Arr, .Match(Tabelle2.Cells(1, 1), col2), 1) avoiding at least the 1st column slice :-)

Collectives™ on Stack Overflow

Index match on 2D array inside VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related