# Best Way to Search a 2D Array

#### mchac

##### Well-known Member
I need to search a 'column' in a 2D array to see if I can find a match to user input value. I'd prefer not to loop

I've found this: http://www.mrexcel.com/forum/excel-questions/63296-visual-basic-applications-function-search-array.html and specifically Rick's solution.

And I've found this: http://www.jpsoftwaretech.com/finding-values-in-an-array-without-looping/.

Both seem to be suitable only for 1D arrays. Is that correct?

Also, what does Chr\$(1) mean (from the first link)?

##### Well-known Member
You could loop through 1 dimension of the array creating a single dimension array each time and searching that in one go.

If you just want to know if something exists ie true or false rather than the location of it you could use Join to join the array to a string then check the len vs the len of the same thing with what you are searching for replaced with nothing, if they differ then it exists.

I can't see how you can do it all in one go across dimensions though

Here is an example:

Code:
``````Sub ArrStuff()
Dim MyArr As Variant, X As Long, FindVal As String
FindVal = "Four"
MyArr = Array(Array("One", "Two", "Three"), Array("Four", "Five", "Six"), Array("Seven", "Eight", "Nine"))
For X = LBound(MyArr) To UBound(MyArr)
If Len(Join(MyArr(X), ",")) <> Len(Replace(Join(MyArr(X), ","), FindVal, "")) Then
MsgBox "Found in Dimension: " & X + 1 '+1 because my option base is 0, take this out if your option base is 1
End If
Next
End Sub``````

##### Well-known Member
Having said that though, if you know how many elements in the first dimension you could do something like this, this works without looping:

Code:
``````Sub ArrStuff()
Dim MyArr As Variant, FindVal As String, StringOfArr As String
FindVal = "Four"
MyArr = Array(Array("One", "Two", "Three"), Array("Four", "Five", "Six"), Array("Seven", "Eight", "Nine"))
StringOfArr = Join(MyArr(0), ",") & "," & Join(MyArr(1), ",") & "," & Join(MyArr(2), ",")
If Len(StringOfArr) <> Len(Replace(StringOfArr, FindVal, "")) Then
MsgBox "Found in array"
End If
End Sub``````
Make a string out of the joined elements. To make it dynamic you would need to loop through dimension 1 if there is no guarantee of the number of elements though.

#### mikerickson

##### MrExcel MVP
The Chr(1) in Ricks code is a delimiter that is not going to be in any of the data strings. Using it as a delimiter makes the routine robust against a user whose array values include common delimiters like space, comma or semi-colon.

If you have a twoD array and are only searching one column, you can use the INDEX function to restrict MATCH to that one column.

Code:
``````Dim myArray(1 To 4, 1 To 3)
Dim rowFound As Variant
Dim searchTerm As String
Dim columnToSearch As Long

myArray(1, 1) = "alpha1": myArray(1, 2) = "alpha2": myArray(1, 3) = "alpha3"
myArray(2, 1) = "beta1": myArray(2, 2) = "beta2": myArray(2, 3) = "beta3"
myArray(3, 1) = "gamma1": myArray(3, 2) = "gamma2": myArray(3, 3) = "gamma3"
myArray(4, 1) = "delta1": myArray(4, 2) = "delta2": myArray(4, 3) = "delta3"

columnToSearch = 2
searchTerm = "gamma2"

rowFound = Application.Match(searchTerm, Application.Index(myArray, 0, columnToSearch), 0)

If IsError(rowFound) Then
Else
MsgBox searchTerm & " found in row " & rowFound & ", column " & columnToSearch
End If``````

#### mchac

##### Well-known Member
Thank you both. I will work through these.

FYI, in researching this I found this article vba - Matching values in string array - Stack Overflow. See Tim Williams' comment and timing found by David Zemens about half way down.

##### Well-known Member
The Chr(1) in Ricks code is a delimiter that is not going to be in any of the data strings. Using it as a delimiter makes the routine robust against a user whose array values include common delimiters like space, comma or semi-colon.
Nice . Saved that little tidbit off for future use #### mchac

##### Well-known Member
Mike what does the '\$' accomplish?

##### Well-known Member
Added in CHR(1) and used Mikes awesome index code.

Still looping dimension1

Code:
``````Sub ArrStuff()
Dim MyArr As Variant, FindVal As String, StringOfArr As String, X As Long
FindVal = "Four"
MyArr = Array(Array("One", "Two", "Three"), Array("Four", "Five", "Six"), Array("Seven", "Eight", "Nine"))
For X = LBound(MyArr) To UBound(MyArr)
If Not IsError(Application.Match(FindVal, Application.Index(MyArr, 0, X + 1), 0)) Then
MsgBox "Found in:" & Application.Match(FindVal, Application.Index(MyArr, 0, X + 1), 0) - 1 & "," & X
Exit For
End If
Next
End Sub``````

Last edited:

#### mikerickson

##### MrExcel MVP
Mike what does the '\$' accomplish?
In Chr\$(1) the \$ is optional notation that the function returns a string value.
It is a hold over from BASIC, where every string function ended in \$

Also, if this array is a range of cells, the worksheet function

=MIN(IF(DATARANGE="searchTerm",ROW(DATARANGE)+COLUMN(DATARANGE)/1000)) will return the row.column of the first cell in DATARANGE that holds searchTerm.

Last edited:

##### Well-known Member
Interestingly enough Mike, your code doesn't like an array where dimension 2 is variable length. I don't however understand why. Hoping you can break it down for me?

In the meantime I have tightened up my original code to give the address and verified it does work with variable length dimensions

Code:
``````Sub ArrStuff2()
Dim MyArr As Variant, X As Long, FindVal As String
FindVal = "Eight"
MyArr = Array(Array("One", "Two", "Three"), Array("Four", "Five", "Six", "Hi"), Array("Seven", "Eight", "Nine"))
For X = LBound(MyArr) To UBound(MyArr)
If Len(Join(MyArr(X), Chr(1))) <> Len(Replace(Join(MyArr(X), Chr(1)), FindVal, "")) Then
MsgBox "Found at location: " & X & "," & Len(Left(Join(MyArr(X), Chr(1)), InStr(1, Join(MyArr(X), Chr(1)), FindVal) - 1)) - Len(Replace(Left(Join(MyArr(X), Chr(1)), InStr(1, Join(MyArr(X), Chr(1)), FindVal) - 1), Chr(1), ""))
End If
Next
End Sub``````

### This Week's Hot Topics

• Importing multiple excel files into one spreadsheet
Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
• find many based on a certain criteria
good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
• How to copy multiple rows using If
Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
• VBA If statement
Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
• Text Format
I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
• Syntax errors
Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...